Goal
You’ll be able to use VLOOKUP and understand its most well-known limitation, then use INDEX/MATCH as the more flexible alternative that avoids that exact limitation.
Learn
VLOOKUP searches for a value in the leftmost column of a range, then returns a corresponding value from a specified column to the right:
=VLOOKUP(A1, D1:F100, 3, FALSE)
This looks for the value in A1 within the first column of D1:F100, then returns the value from the third column of that range, in the same matching row. The final FALSE requests an exact match (almost always what you want) rather than an approximate match.
Here’s VLOOKUP’s most well-known limitation: it can only look to the right of the column it searches. If the value you need to return sits in a column to the left of your lookup column, VLOOKUP simply can’t reach it at all — no configuration option fixes this.
INDEX and MATCH, combined, solve this directly:
=INDEX(C1:C100, MATCH(A1, D1:D100, 0))
MATCH finds the position of A1 within D1:D100; INDEX then returns the value at that same position from C1:C100 — a completely different range, which can sit anywhere at all relative to the lookup column, including to its left. This flexibility is exactly why many experienced Excel users prefer INDEX/MATCH over VLOOKUP as their default lookup approach, despite VLOOKUP’s simpler-looking syntax.
Decision Task
Your data has Employee ID in column C, and Employee Name in column A (to the left of the ID). You need to look up a name based on a known ID. Before reading on: can VLOOKUP handle this directly, and if not, why not?
Show Answer
No, not directly — VLOOKUP can only search a column and return a value from a column to its right, but Name (column A) sits to the left of ID (column C), the column you’d be searching. INDEX/MATCH handles this correctly, since it has no such directional restriction — MATCH would find the ID’s position in column C, and INDEX would return the corresponding value from column A regardless of it sitting to the left.
Common Mistake
Restructuring an entire spreadsheet’s column order just to make VLOOKUP work, rather than switching to INDEX/MATCH, which has no such directional limitation at all. Reorganizing real data specifically to accommodate one function’s limitation is usually more disruptive than simply using the more flexible tool that doesn’t have that limitation in the first place.
Practice Questions
1. Write a VLOOKUP formula that looks up the value in A1 within range D1:G50, returning the value from the 4th column of that range, requiring an exact match.
Show Answer
=VLOOKUP(A1, D1:G50, 4, FALSE)
2. What is VLOOKUP’s well-known directional limitation?
Show Answer
It can only return a value from a column to the right of the column it searches — it cannot look left.
3. Write an INDEX/MATCH formula that looks up A1 within D1:D50 and returns the corresponding value from B1:B50.
Show Answer
=INDEX(B1:B50, MATCH(A1, D1:D50, 0))
4. True or False: INDEX/MATCH can return a value from a column to the left of the column being searched, unlike VLOOKUP.
Show Answer
True — this is exactly the specific advantage INDEX/MATCH has over VLOOKUP.
5. What does the FALSE at the end of a VLOOKUP formula specify?
Show Answer
That an exact match is required, rather than an approximate match.
Try It Yourself
Without looking back, explain in your own words why many experienced Excel users default to INDEX/MATCH over VLOOKUP, even though VLOOKUP’s syntax looks simpler at first glance.
Show Answer
INDEX/MATCH has no directional limitation — it can return a value from any column relative to the lookup column, including to the left, which VLOOKUP genuinely cannot do at all, making INDEX/MATCH the more flexible, future-proof default choice even though it requires slightly more complex syntax upfront.
Quick Check
1. What does VLOOKUP search, and what does it return?
Show Answer
It searches the leftmost column of a range, and returns a value from a specified column to the right of it, in the matching row.
2. What is VLOOKUP’s most well-known limitation?
Show Answer
It cannot return a value from a column to the left of the column it searches.
3. What does MATCH find?
Show Answer
The position of a value within a specified range.
4. What does INDEX return, given a range and a position?
Show Answer
The value at that specific position within the given range.
5. Why does INDEX/MATCH avoid VLOOKUP’s directional limitation?
Show Answer
The lookup range (for MATCH) and the return range (for INDEX) are entirely separate, independent ranges, with no requirement about their relative left/right position.