index match function help me

[Guest]

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

 

[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

 

[Guest]

it comes up with value wen i use that formula any other suggestions

 

[Biff]

Did you enter the formula as an array?

Post the EXACT formula that used.

Biff