Quirk with VLOOKUP (2003 SP2)

Topic

New Reply

My spreadsheet has a range of numbers illustrated by example in the attached file. The values in the range rise steadily to a maximum, then decline steadily after that. I want to find the row number where the value is some arbitrary amount less than the maximum value and have used VLOOKUP to accomplish the task. In the attached spreadsheet, enter a value of “100”, and both instances of vlookup work properly. by returning “10”

However when searching for “170”, the second instance returns a value of “23”, and when searching for “200”, it returns a value of “30”. I can understand that the VLOOKUP function would fail for values “on the downhill slope beyond the maximum”, but it seems to fail even for values less than the maximum.

Is this a bug in VLOOKUP, or is there another way to accomplish the task? In the real spreadsheet, the spreadsheet represents a cost versus age function. I want to find the range of ages where the cost is within a specified tolerance of the maximum value.

Reply | Quote

Replies

The reason that the lookup table must be sorted into assending order when using the “fuzzy” search is that the search algorithm uses a binary search. Therefore, it can get a hit anywhere in the table if the table is not sorted.

Reply | Quote

Aha! Binary search. That makes sense why it was failing inpredictably. Thanks for the insight.

Reply | Quote

If you omit the 4th argument of VLOOKUP, the first column of the lookup array should be sorted in ascending order. If it isn’t, the result is unpredictable. You could use

=MATCH(A4,L9:L38,0)

but this will fail if there is no exact match.

Reply | Quote

Try these array formulas (confirm both with Ctrl+Shift+Enter):
First index of number greater than or equal to that in A4:

=MIN(IF(A9:A38>=A4,ROW($1:$30)))

Last index of etc.:

=MAX(IF(A9:A38>=A4,ROW($1:$30)))

The highest number in the ROW function should equal the number of entries in your data column.

Reply | Quote

Very Cool! I had used array formulae ONCE, many years ago, but never really understood them. Was baffled by your method, and doubly-baffled by the explanation in MS Help. Anyway, following your directions *literally* was all that it took to make it work. Thanks very much.

Reply | Quote

A nice way to understand what is happening is as follows:
– Select one of the cells with the array formulas.
– Press F2 to edit the formula.
– Select A9:A38>=A4 and press F9 to evaluate this expression,
– You’ll see an array of TRUE/FALSE values.
– Select ROW($1:$30) and press F9 to evaluate it.
– You’ll see an array of the numbers 1 to 30.
– Now select the entire IF(…) expression and press F9.
– You’ll see an array existing of FALSE for values that don’t meet the condition, and the index number for values that do.
– The MIN function only looks at numbers, so it ignores the FALSE values and returns the smallest index number, similar for MAX.
– Press Esc to avoid saving the evaluated expressions.

Reply | Quote