Index & Match on greater than

[Ang Ang]

Hi Excel,

Not sure what I am doing incorrect. My index and match won't work on greater than. It works on exact or less than. Attached is the file.

Thanks,

 

[Hui]

This is a common error using the Match function

If you read the Help for Match


You will see why it is not working

You maybe able to rearrange your data by sorting on Column B if that is what you require

 

[NARAYANK991]

Hi ,

Since your data is in ascending order , the third parameter of the MATCH function , which has been put to -1 , should be 1 (+1).

Narayan

 

[AlanSidman]

It is an arrangement issue. For -1 the values need to be in descending order. Yours are in ascending order.

 

[Ang Ang]

Thank you all,

Got it. So is there a formula to look up the next highest value without rearrange my data?

Thanks

 

[Hui]

Does this work for you ?

G2: =INDEX(A2:A7,MATCH(F2,B2:B7,1)+1)