Why INDEX-MATCH Beats VLOOKUP
See the speed and flexibility advantages over column-based lookups.
A Quick VLOOKUP Refresher
VLOOKUP searches the first column of a table and returns a value from a column to its right, identified by a number.
For example =VLOOKUP(E1, A2:D20, 3, FALSE) finds E1 in column A and returns the value from the 3rd column of the table.
It is popular and simple, but it carries some real limitations. INDEX-MATCH avoids every one of them, as you will see.
=VLOOKUP(E1, A2:D20, 3, FALSE)Limit 1: VLOOKUP Only Looks Right
VLOOKUP must search the leftmost column of its table and can only return values to the right of it. It cannot look left.
If your IDs are in column C and the names you want are in column A, VLOOKUP is stuck.
INDEX-MATCH has no such rule. =INDEX(A2:A20, MATCH(E1, C2:C20, 0)) searches column C and returns from column A without any workaround.
=INDEX(A2:A20, MATCH(E1, C2:C20, 0))All lessons in this course
- Pulling Values With INDEX
- Finding Positions With MATCH
- Combining INDEX and MATCH
- Why INDEX-MATCH Beats VLOOKUP