20. Excel Tips - VLOOKUP limitations - can use INDEX + MATCH instead

Nick's picture

VLOOKUP is all very well when the data is arranged in the right way for the function - with the thing you're looking for to the left of the thing you want to return, but what happens if you want to do the reverse of VLOOKUP ?

Well, fortunately help is at hand with INDEX + MATCH.

MATCH returns the position of a value in a range INDEX returns the value of a cell that is offset from another.

Combined, you can use INDEX + MATCH to simulate VLOOKUP, and to do the reverse lookup as well as the VLOOKUP:


Index Match Spreadsheet


IndexMatch.xls17.5 KB

Help to write a excel function to retrieve value from a table


I'm working on a table to retrieve a value based on Object's name (multiple objects with random dates); say for example, I need a value in a row with specific object with entry for latest date up to 5th Jun 2017 only ( not to exceed the date). Can anyone help to guide?

Not capturing

When am useing Vlookup, it is not capturing the correct data. i tried to take it as True, false or other sheet also.. if u want i sent the file

thanks buddy

it is very helpful


Index(Match(,,0)) has further advantages.

So avoid VLookUp.


This does work great, but

What happens when two people earn $200

Nick's picture

2 people earn 200

the idea is that you lookup salaries, not names..
salaries have to be unique for it to work the other way round.