index match function help me

  • Thread starter vlookup help pls
  • Start date
V

vlookup help pls

i have this formula in my work book but what it does is gets the closest
value which is exact or higer than the linkcell value but i want it to pick
the closest value wether its higher or lower

=INDEX(Sheet3!A1:A29,MATCH(B34,Sheet3!A1:A29,2),1)

need help pls

regards keith
 
B

Biff

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(Sheet3!A1:A29,MATCH(MIN(ABS(B34-Sheet3!A1:A29)),ABS(B34-Sheet3!A1:A29),0))

It'll return the first instance of the "closest" value:

B34 = 22

A1 = 24
A2 = 37
A3 = 20

In this case A1 would be returned. Both A1 and A3 have a difference from the
lookup value of 2 but A1 is the first instance of that closest value.

Biff
 
Top