In this situation, manually copying Column A to Column C (moving the lookup to the right) and then running VLOOKUP is too inefficient. If there are many rows to copy, it could even crash the computer...
In this case, copying Column A to Column C (moving the lookup to the right) and then running VLOOKUP is inefficient.
So this time we'll introduce INDEX + MATCH to replace VLOOKUP.
If we want to use "30" to find "C", we actually need the 4th cell in Column A, and since 30 happens to be in the 4th cell of Column B, if we can find this "4" we can likely complete the task. To find this 4, use MATCH.
=MATCH(30,B:B,1)That means telling Excel to find which position in Column B contains 30 — it's 4. Then we use that 4 as the row number for Column A to retrieve its 4th cell, which is done with INDEX.
=INDEX(A:A,MATCH(30,B:B,1))So you can see INDEX + MATCH achieves VLOOKUP's effect without being restricted by lookup direction!
Also, a reminder that this weekend's offer 【Early bird discount: $200 off】 ends soon!Excel Functions Workshop:
- Introduce and explain the usage of nearly 80 functions
- Explore their usage and characteristics through common workplace problems
- Help you establish relationships between datasets
- Teach you how to solve many of the problems you encounter at work
