20. Excel Tips - VLOOKUP limitations - can use INDEX + MATCH instead
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:
Training Video on VLOOKUP INDEX MATCH
| Attachment | Size |
|---|---|
| IndexMatch.xls | 17.5 KB |
»
- Nick's blog
- Login or register to post comments
- 62710 reads
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
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.