Excel Lookups — VLOOKUP, XLOOKUP & INDEX-MATCH
Lookups fetch a value from one table into another — the single most-used analyst skill after basic formulas, and a guaranteed interview topic.
Learning Objectives
- Use VLOOKUP and explain its syntax, including exact vs approximate match.
- List VLOOKUP's key limitations (can't look left, first-column key).
- Use XLOOKUP as the modern replacement that works in any direction.
- Build flexible lookups with INDEX + MATCH.
- Handle lookup errors with
IFERROR.
0. The Reference Table
We'll use this products price list as the table we "look up into" throughout the session.
| A (product_id) | B (product_name) | C (price) | |
|---|---|---|---|
| 2 | P001 | Pen | 25 |
| 3 | P002 | Notebook | 120 |
| 4 | P003 | Eraser | 10 |
| 5 | P004 | Marker | 45 |
The lookup key is product_id (column A). We want to pull the price (column C) given a product ID.
1. VLOOKUP — the Classic
VLOOKUP searches down the first column of a range and returns a value
from a column to its right.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | Meaning |
|---|---|
lookup_value | what to search for (e.g. "P002") |
table_array | the range to search in |
col_index_num | which column to return from (1 = first) |
range_lookup | FALSE = exact, TRUE = approximate |
-- Look up the price of P002 (price is the 3rd column)
=VLOOKUP("P002", A2:C5, 3, FALSE) → 120range_lookup argument defaults to
TRUE (approximate) if you leave it out — which silently returns the wrong value. Almost always
pass FALSE for an exact match.
2. VLOOKUP's Limitations
Interviewers love this question. VLOOKUP has two big constraints:
- It can only look to the right. The value you want must be in a column to the right of the lookup key. You can't look "left."
- The lookup key must be in the first column of the table range.
If your key is in column C and you need data from column B, plain VLOOKUP can't do it.
3. XLOOKUP — the Modern Replacement
XLOOKUP fixes VLOOKUP's limitations: it works in any direction (left, right, up,
down), and it defaults to exact match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])-- Same lookup, cleaner syntax (no column index needed)
=XLOOKUP("P002", A2:A5, C2:C5) → 120
-- Even a "look to the left" works:
=XLOOKUP("P002", A2:A5, B2:B5) → Notebook (name is left of nothing here, but direction no longer matters)=XLOOKUP("P999", A2:A5, C2:C5, "Not found"). No need for a separate IFERROR.
4. INDEX + MATCH — the Flexible Classic
Before XLOOKUP, INDEX + MATCH was the go-to for flexible lookups. Understanding it
shows real mastery.
MATCH — finds the position
=MATCH(lookup_value, lookup_array, 0)=MATCH("P002", A2:A5, 0) → 2 (P002 is the 2nd row in the range)INDEX — returns the value at a position
=INDEX(range, row_num, [col_num])=INDEX(C2:C5, 2) → 120 (2nd value in the price column)Together
=INDEX(C2:C5, MATCH("P002", A2:A5, 0)) → 1205. VLOOKUP vs XLOOKUP vs INDEX-MATCH
| Feature | VLOOKUP | INDEX-MATCH | XLOOKUP |
|---|---|---|---|
| Looks left | ❌ No | ✅ Yes | ✅ Yes |
| Default match | Approximate (TRUE) | Exact (0) | Exact |
| Ease of use | Simple | Harder | Simplest |
| Column order dependence | Yes (key must be 1st) | No | No |
| Availability | All versions | All versions | Excel 365 / 2021+ |
In interviews: name VLOOKUP for legacy, INDEX-MATCH for flexibility, XLOOKUP for modern simplicity.
6. Handling Errors with IFERROR
When a lookup fails (value not found), Excel returns #N/A. Wrap it to show something cleaner.
=IFERROR(VLOOKUP("P999", A2:C5, 3, FALSE), "Not found")This shows "Not found" instead of an ugly #N/A error.
7. Interview Questions (with Model Answers)
Lookups are a guaranteed interview topic. Self-test before revealing.
IQ1. What is VLOOKUP, and how do you use it?
Model answer: "VLOOKUP searches down the first column of a range for a value and returns a value from a column to its right. I pass the lookup value, the table range, the column number to return, and FALSE for an exact match."
IQ2. What's the difference between exact (FALSE) and approximate (TRUE) match in VLOOKUP?
Model answer: "FALSE returns only an exact match; TRUE returns the closest match and requires the first column to be sorted. The default is TRUE, which is why I always pass FALSE explicitly — otherwise you can silently get the wrong value."
IQ3. What are the main limitations of VLOOKUP?
Model answer: "It can only look to the right — the value I want must be in a column to the right of the lookup key, and the key must be in the first column. It's also not case-sensitive and returns the first match only."
IQ4. What's the difference between VLOOKUP and INDEX-MATCH?
Model answer: "INDEX-MATCH separates the 'find position' step (MATCH) from the 'return value' step (INDEX), so it can look in any direction — including left — and isn't tied to column order. VLOOKUP is simpler but limited to rightward lookups."
IQ5. What is XLOOKUP, and how does it improve on VLOOKUP?
Model answer: "XLOOKUP is the modern replacement. It works in any direction, defaults to exact match, and lets me specify a 'not found' message directly. It removes VLOOKUP's leftward limitation."
IQ6. How would you do a 'lookup to the left' that VLOOKUP can't do?
Model answer: "I'd use INDEX-MATCH or XLOOKUP — both allow the return column to be independent of the lookup column, so I can return a value from the left of the key."
IQ7. Explain how INDEX and MATCH work, separately and together.
Model answer: "MATCH returns the position of a value in a range. INDEX returns the value at a given position. Together, INDEX(C2:C5, MATCH("P002", A2:A5, 0)) finds the row of P002 and returns the corresponding price."
IQ8. What's the default value of VLOOKUP's last parameter, and why does it matter?
Model answer: "It defaults to TRUE (approximate match). That matters because approximate matching can return the wrong value if the data isn't sorted. For exact lookups I always pass FALSE."
IQ9. How do you handle #N/A errors in a lookup?
Model answer: "I wrap the lookup in IFERROR — like =IFERROR(VLOOKUP(...), "Not found") — to return a clean message instead of the error. With XLOOKUP, I can pass the 'not found' value directly."
Hands-On Project: Look Up Product Prices
You have a products price list (columns A–C) and an orders sheet that lists
product IDs with quantities. Build formulas to pull prices and compute totals.
Steps
- Write a VLOOKUP to return the price of product P003.
- Write an XLOOKUP to return the name of product P001.
- Write an INDEX-MATCH to return the price of P004.
- Write a formula that returns "Not found" for a missing product ID using IFERROR.
- Explain which of the three you'd use and why.
View Solution / Walkthrough
-- 1. VLOOKUP price of P003
=VLOOKUP("P003", A2:C5, 3, FALSE) → 10
-- 2. XLOOKUP name of P001
=XLOOKUP("P001", A2:A5, B2:B5) → Pen
-- 3. INDEX-MATCH price of P004
=INDEX(C2:C5, MATCH("P004", A2:A5, 0)) → 45
-- 4. IFERROR for a missing ID
=IFERROR(VLOOKUP("P999", A2:C5, 3, FALSE), "Not found") → Not foundWhich to use: XLOOKUP if you have a modern version (simplest, no limitations); INDEX-MATCH for maximum compatibility and flexibility; VLOOKUP only for quick, simple, rightward lookups.
Key Takeaways
VLOOKUP is simple but limited — can only look right, key must be first column.
Always pass FALSE for exact match — the default TRUE is a silent trap.
XLOOKUP works any direction and defaults to exact — the modern choice.
INDEX-MATCH separates "find" from "return" — maximum flexibility.
Wrap lookups in IFERROR to handle missing values gracefully.
Objective Questions — Test Your Understanding
Q1. In VLOOKUP, what does the 4th argument FALSE specify?
Q2. What is a key limitation of VLOOKUP?
Q3. Which function works in any direction and defaults to exact match?
Q4. In INDEX-MATCH, what does the MATCH function return?
Q5. Which lookup combination is most flexible for a leftward lookup?