MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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