MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Use Index and Match to Lookup a Cell to the Left of Key Cell


May 23, 2002 - by Juan Pablo Gonzalez

Daniel asks:

A B
1 14 20
2 12 25
3 10 35
4 8 50
5 4 65

I have a cell at let say E5 s equal to 30 the next number up is 35 and i want to display the 10. If E5 is equal to 55 than i want it to display 4. What is the formula for this?

If the data is located in A2:B6, then use this formula:

=INDEX($A$2:$A$6,MATCH(E5,$B$2:$B$6)+1)