Results 1 to 7 of 7
Thread: Quirk with VLOOKUP (2003 SP2)

20070531, 15:12 #1
 Join Date
 Jan 2001
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 632
 Thanks
 0
 Thanked 0 Times in 0 Posts
Quirk with VLOOKUP (2003 SP2)
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.
Jack MacDonald
Vancouver, Canada

20070531, 15:19 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Quirk with VLOOKUP (2003 SP2)
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.
Legare Coleman

20070531, 15:36 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Quirk with VLOOKUP (2003 SP2)
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.

20070531, 15:54 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Quirk with VLOOKUP (2003 SP2)
Try these array formulas (confirm both with Ctrl+Shift+Enter):
First index of number greater than or equal to that in A4:
<code>
=MIN(IF(A9:A38>=A4,ROW($1:$30)))
</code>
Last index of etc.:
<code>
=MAX(IF(A9:A38>=A4,ROW($1:$30)))
</code>
The highest number in the ROW function should equal the number of entries in your data column.

20070531, 16:04 #5
 Join Date
 Jan 2001
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 632
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Quirk with VLOOKUP (2003 SP2)
Aha! Binary search. That makes sense why it was failing inpredictably. Thanks for the insight.

Jack MacDonald
Vancouver, Canada

20070531, 16:16 #6
 Join Date
 Jan 2001
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 632
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Quirk with VLOOKUP (2003 SP2)
Very Cool! I had used array formulae ONCE, many years ago, but never really understood them. Was baffled by your method, and doublybaffled by the explanation in MS Help. Anyway, following your directions *literally* was all that it took to make it work. Thanks very much.

Jack MacDonald
Vancouver, Canada

20070531, 16:29 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Quirk with VLOOKUP (2003 SP2)
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.