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

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

Hi,

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

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.