/**
* Finds first (lowest index) exact occurrence of specified value.
* @param lookupComparer the value to be found in column or row vector
* @param vector the values to be searched. For VLOOKUP this Is the first column of the
* tableArray. For HLOOKUP this Is the first row of the tableArray.
* @return zero based index into the vector, -1 if value cannot be found
*/
private static int lookupFirstIndexOfValue(LookupValueComparer lookupComparer, ValueVector vector, MatchMode matchMode)
{
// Find first occurrence of lookup value
int size = vector.Size;
int bestMatchIdx = -1;
ValueEval bestMatchEval = null;
for (int i = 0; i < size; i++)
{
ValueEval valueEval = vector.GetItem(i);
CompareResult result = lookupComparer.CompareTo(valueEval);
if (result.IsEqual)
{
return(i);
}
switch (matchMode)
{
case MatchMode.ExactMatchFallbackToLargerValue:
if (result.IsLessThan)
{
if (bestMatchEval == null)
{
bestMatchIdx = i;
bestMatchEval = valueEval;
}
else
{
LookupValueComparer matchComparer = CreateTolerantLookupComparer(valueEval, true, true);
if (matchComparer.CompareTo(bestMatchEval).IsLessThan)
{
bestMatchIdx = i;
bestMatchEval = valueEval;
}
}
}
break;
case MatchMode.ExactMatchFallbackToSmallerValue:
if (result.IsGreaterThan)
{
if (bestMatchEval == null)
{
bestMatchIdx = i;
bestMatchEval = valueEval;
}
else
{
LookupValueComparer matchComparer = CreateTolerantLookupComparer(valueEval, true, true);
if (matchComparer.CompareTo(bestMatchEval).IsGreaterThan)
{
bestMatchIdx = i;
bestMatchEval = valueEval;
}
}
}
break;
}
}
return(bestMatchIdx);
}
/**
* Excel has funny behaviour when the some elements in the search vector are the wrong type.
*
*/
private static int PerformBinarySearch(ValueVector vector, LookupValueComparer lookupComparer)
{
// both low and high indexes point to values assumed too low and too high.
BinarySearchIndexes bsi = new BinarySearchIndexes(vector.Size);
while (true)
{
int midIx = bsi.GetMidIx();
if (midIx < 0)
{
return(bsi.GetLowIx());
}
CompareResult cr = lookupComparer.CompareTo(vector.GetItem(midIx));
if (cr.IsTypeMismatch)
{
int newMidIx = HandleMidValueTypeMismatch(lookupComparer, vector, bsi, midIx);
if (newMidIx < 0)
{
continue;
}
midIx = newMidIx;
cr = lookupComparer.CompareTo(vector.GetItem(midIx));
}
if (cr.IsEqual)
{
return(FindLastIndexInRunOfEqualValues(lookupComparer, vector, midIx, bsi.GetHighIx()));
}
bsi.narrowSearch(midIx, cr.IsLessThan);
}
}
/**
* @return zero based index
*/
private static int FindIndexOfValue(ValueEval lookupValue, ValueVector lookupRange,
bool matchExact, bool FindLargestLessThanOrEqual)
{
LookupValueComparer lookupComparer = CreateLookupComparer(lookupValue, matchExact);
int size = lookupRange.Size;
if (matchExact)
{
for (int i = 0; i < size; i++)
{
if (lookupComparer.CompareTo(lookupRange.GetItem(i)).IsEqual)
{
return(i);
}
}
throw new EvaluationException(ErrorEval.NA);
}
if (FindLargestLessThanOrEqual)
{
// Note - backward iteration
for (int i = size - 1; i >= 0; i--)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsTypeMismatch)
{
continue;
}
if (!cmp.IsLessThan)
{
return(i);
}
}
throw new EvaluationException(ErrorEval.NA);
}
// else - Find smallest greater than or equal to
// TODO - Is binary search used for (match_type==+1) ?
for (int i = 0; i < size; i++)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsEqual)
{
return(i);
}
if (cmp.IsGreaterThan)
{
if (i < 1)
{
throw new EvaluationException(ErrorEval.NA);
}
return(i - 1);
}
}
return(size - 1);
}
/**
* Once the binary search has found a single match, (V/H)LOOKUP steps one by one over subsequent
* values to choose the last matching item.
*/
private static int FindLastIndexInRunOfEqualValues(LookupValueComparer lookupComparer, ValueVector vector,
int firstFoundIndex, int maxIx)
{
for (int i = firstFoundIndex + 1; i < maxIx; i++)
{
if (!lookupComparer.CompareTo(vector.GetItem(i)).IsEqual)
{
return(i - 1);
}
}
return(maxIx - 1);
}
/**
* Finds first (lowest index) exact occurrence of specified value.
* @param lookupValue the value to be found in column or row vector
* @param vector the values to be searched. For VLOOKUP this Is the first column of the
* tableArray. For HLOOKUP this Is the first row of the tableArray.
* @return zero based index into the vector, -1 if value cannot be found
*/
private static int LookupIndexOfExactValue(LookupValueComparer lookupComparer, ValueVector vector)
{
// Find first occurrence of lookup value
int size = vector.Size;
for (int i = 0; i < size; i++)
{
if (lookupComparer.CompareTo(vector.GetItem(i)).IsEqual)
{
return(i);
}
}
return(-1);
}
public static int LookupIndexOfValue(ValueEval lookupValue, ValueVector vector, bool IsRangeLookup)
{
LookupValueComparer lookupComparer = CreateLookupComparer(lookupValue);
int result;
if (IsRangeLookup)
{
result = PerformBinarySearch(vector, lookupComparer);
}
else
{
result = LookupIndexOfExactValue(lookupComparer, vector);
}
if (result < 0)
{
throw new EvaluationException(ErrorEval.NA);
}
return(result);
}
public static int XlookupIndexOfValue(ValueEval lookupValue, ValueVector vector, MatchMode matchMode, SearchMode searchMode)
{
LookupValueComparer lookupComparer = CreateTolerantLookupComparer(lookupValue, true, true);
int result;
if (searchMode == SearchMode.IterateBackward || searchMode == SearchMode.BinarySearchBackward)
{
result = lookupLastIndexOfValue(lookupComparer, vector, matchMode);
}
else
{
result = lookupFirstIndexOfValue(lookupComparer, vector, matchMode);
}
if (result < 0)
{
throw new EvaluationException(ErrorEval.NA);
}
return(result);
}
public static int lookupFirstIndexOfValue(ValueEval lookupValue, ValueVector vector, bool isRangeLookup)
{
LookupValueComparer lookupComparer = CreateLookupComparer(lookupValue, isRangeLookup, false);
int result;
if (isRangeLookup)
{
result = PerformBinarySearch(vector, lookupComparer);
}
else
{
result = lookupFirstIndexOfValue(lookupComparer, vector, MatchMode.ExactMatch);
}
if (result < 0)
{
throw new EvaluationException(ErrorEval.NA);
}
return(result);
}
/**
* Excel seems to handle mismatched types initially by just stepping 'mid' ix forward to the
* first compatible value.
* @param midIx 'mid' index (value which has the wrong type)
* @return usually -1, signifying that the BinarySearchIndex has been narrowed to the new mid
* index. Zero or greater signifies that an exact match for the lookup value was found
*/
private static int HandleMidValueTypeMismatch(LookupValueComparer lookupComparer, ValueVector vector,
BinarySearchIndexes bsi, int midIx)
{
int newMid = midIx;
int highIx = bsi.GetHighIx();
while (true)
{
newMid++;
if (newMid == highIx)
{
// every element from midIx to highIx was the wrong type
// move highIx down to the low end of the mid values
bsi.narrowSearch(midIx, true);
return(-1);
}
CompareResult cr = lookupComparer.CompareTo(vector.GetItem(newMid));
if (cr.IsLessThan && newMid == highIx - 1)
{
// move highIx down to the low end of the mid values
bsi.narrowSearch(midIx, true);
return(-1);
// but only when "newMid == highIx-1"? slightly weird.
// It would seem more efficient to always do this.
}
if (cr.IsTypeMismatch)
{
// keep stepping over values Until the right type Is found
continue;
}
if (cr.IsEqual)
{
return(newMid);
}
// Note - if moving highIx down (due to lookup<vector[newMid]),
// this execution path only moves highIx it down as far as newMid, not midIx,
// which would be more efficient.
bsi.narrowSearch(newMid, cr.IsLessThan);
return(-1);
}
}
/**
* Once the binary search has found a single match, (V/H)LOOKUP steps one by one over subsequent
* values to choose the last matching item.
*/
private static int FindLastIndexInRunOfEqualValues(LookupValueComparer lookupComparer, ValueVector vector,
int firstFoundIndex, int maxIx)
{
for (int i = firstFoundIndex + 1; i < maxIx; i++)
{
if (!lookupComparer.CompareTo(vector.GetItem(i)).IsEqual)
{
return i - 1;
}
}
return maxIx - 1;
}
/**
* Excel seems to handle mismatched types initially by just stepping 'mid' ix forward to the
* first compatible value.
* @param midIx 'mid' index (value which has the wrong type)
* @return usually -1, signifying that the BinarySearchIndex has been narrowed to the new mid
* index. Zero or greater signifies that an exact match for the lookup value was found
*/
private static int HandleMidValueTypeMismatch(LookupValueComparer lookupComparer, ValueVector vector,
BinarySearchIndexes bsi, int midIx)
{
int newMid = midIx;
int highIx = bsi.GetHighIx();
while (true)
{
newMid++;
if (newMid == highIx)
{
// every element from midIx to highIx was the wrong type
// move highIx down to the low end of the mid values
bsi.narrowSearch(midIx, true);
return -1;
}
CompareResult cr = lookupComparer.CompareTo(vector.GetItem(newMid));
if (cr.IsLessThan && newMid == highIx - 1)
{
// move highIx down to the low end of the mid values
bsi.narrowSearch(midIx, true);
return -1;
// but only when "newMid == highIx-1"? slightly weird.
// It would seem more efficient to always do this.
}
if (cr.IsTypeMismatch)
{
// keep stepping over values Until the right type Is found
continue;
}
if (cr.IsEqual)
{
return newMid;
}
// Note - if moving highIx down (due to lookup<vector[newMid]),
// this execution path only moves highIx it down as far as newMid, not midIx,
// which would be more efficient.
bsi.narrowSearch(newMid, cr.IsLessThan);
return -1;
}
}
/**
* Excel has funny behaviour when the some elements in the search vector are the wrong type.
*
*/
private static int PerformBinarySearch(ValueVector vector, LookupValueComparer lookupComparer)
{
// both low and high indexes point to values assumed too low and too high.
BinarySearchIndexes bsi = new BinarySearchIndexes(vector.Size);
while (true)
{
int midIx = bsi.GetMidIx();
if (midIx < 0)
{
return bsi.GetLowIx();
}
CompareResult cr = lookupComparer.CompareTo(vector.GetItem(midIx));
if (cr.IsTypeMismatch)
{
int newMidIx = HandleMidValueTypeMismatch(lookupComparer, vector, bsi, midIx);
if (newMidIx < 0)
{
continue;
}
midIx = newMidIx;
cr = lookupComparer.CompareTo(vector.GetItem(midIx));
}
if (cr.IsEqual)
{
return FindLastIndexInRunOfEqualValues(lookupComparer, vector, midIx, bsi.GetHighIx());
}
bsi.narrowSearch(midIx, cr.IsLessThan);
}
}
/**
* Finds first (lowest index) exact occurrence of specified value.
* @param lookupValue the value to be found in column or row vector
* @param vector the values to be searched. For VLOOKUP this Is the first column of the
* tableArray. For HLOOKUP this Is the first row of the tableArray.
* @return zero based index into the vector, -1 if value cannot be found
*/
private static int LookupIndexOfExactValue(LookupValueComparer lookupComparer, ValueVector vector)
{
// Find first occurrence of lookup value
int size = vector.Size;
for (int i = 0; i < size; i++)
{
if (lookupComparer.CompareTo(vector.GetItem(i)).IsEqual)
{
return i;
}
}
return -1;
}
/**
* @return zero based index
*/
private static int FindIndexOfValue(ValueEval lookupValue, ValueVector lookupRange,
bool matchExact, bool FindLargestLessThanOrEqual)
{
LookupValueComparer lookupComparer = CreateLookupComparer(lookupValue, matchExact);
int size = lookupRange.Size;
if (matchExact)
{
for (int i = 0; i < size; i++)
{
if (lookupComparer.CompareTo(lookupRange.GetItem(i)).IsEqual)
{
return(i);
}
}
throw new EvaluationException(ErrorEval.NA);
}
if (FindLargestLessThanOrEqual)
{
//in case the lookupRange area is not sorted, still try to get the right index
if (lookupValue is NumericValueEval nve)
{
Dictionary <int, double> dicDeltas = new Dictionary <int, double>();
for (int i = 0; i < size; i++)
{
var item = lookupRange.GetItem(i) as NumericValueEval;
if (lookupComparer.CompareTo(item).IsEqual)
{
return(i);
}
else
{
dicDeltas.Add(i, item.NumberValue - nve.NumberValue);
}
}
return(dicDeltas.Where(kv => kv.Value < 0).OrderByDescending(kv => kv.Value).First().Key);
}
// Note - backward iteration
for (int i = size - 1; i >= 0; i--)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsTypeMismatch)
{
continue;
}
if (!cmp.IsLessThan)
{
return(i);
}
}
throw new EvaluationException(ErrorEval.NA);
}
// else - Find smallest greater than or equal to
// TODO - Is binary search used for (match_type==+1) ?
for (int i = 0; i < size; i++)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsEqual)
{
return(i);
}
if (cmp.IsGreaterThan)
{
if (i < 1)
{
throw new EvaluationException(ErrorEval.NA);
}
return(i - 1);
}
}
return(size - 1);
}
