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
===============
