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
- Add new comment
- 11219 reads
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.