MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookup function help


Posted by Ken on November 20, 2001 3:33 PM

Assume that my range
B2 contains 6.3
B3 contains 7.5
B4 contains 8.0 and so on...
Column B is sorted in an ascending order. My lookup value is 7.3.
If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value. So it return 6.3.
Can I get the nearest value to the lookup value?

Thank you


Posted by Aladin Akyurek on November 20, 2001 4:05 PM

Ken --

The following array-formula may be what you're looking for:

=IF(ISNUMBER(MATCH(C2,B2:B10,0)),INDEX(B2:B10,MATCH(C2,B2:B10,0)),MIN(IF((B2:B10>=C2-0.1)*(B2:B10<=C2+0.1),B2:B10)))

C2 houses your numeric lookup value, B2:B10 your numeric data.

Aladin

===============