vlookup like comparison within a range
Posted by Anjani on September 28, 2001 1:37 PM
Is there a function like vlookup that searches through a range to find a number that is within say +/- 0.1 of the number it is trying to find?
Posted by Aladin Akyurek on October 01, 2001 4:41 AM
Can be done with array formulas.
Use either (1)
=IF(ISNUMBER(MATCH(B2,A2:A7,0)),INDEX(A2:A7,MATCH(B2,A2:A7,0)),MAX(IF((A2:A7>=B2-0.1)*(A2:A7<=B2+0.1),A2:A7)))
or (2)
=IF(ISNUMBER(MATCH(B2,A2:A7,0)),INDEX(A2:A7,MATCH(B2,A2:A7,0)),MIN(IF((A2:A7>=B2-0.1)*(A2:A7<=B2+0.1),A2:A7)))
B2 contains your serach value.
Note. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.
Aladin
