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:

Pic

Index Match Spreadsheet

Training Video on VLOOKUP INDEX MATCH

AttachmentSize
IndexMatch.xls17.5 KB

thanks buddy

it is very helpful

excellent

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

So avoid VLookUp.

Dominique HANDELSMAN
Paris

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.