If you searched for INDEX MATCH or XLOOKUP, something has probably already gone wrong with a VLOOKUP. This page is about the two structural limits that cause it: VLOOKUP can only return columns to the right, and its column index is a number that does not update when someone inserts a column — so the formula quietly starts returning different data with no error at all.
INDEX/MATCH removes both, at the cost of a slightly longer formula. XLOOKUP removes both plus the approximate-match trap and the IFERROR wrapper, at the cost of needing Microsoft 365 or Excel 2021 and later. Neither, however, protects you from the one mistake people carry over: leaving out the argument that forces an exact match.
The problems below are the decisions you actually face — which to use on a workbook you share, what happens when a column is inserted, why a rewritten formula still returns the wrong row. Work each one before opening the explanation.
- Questions
- 5 questions
- Groups
- 3 groups
- Access
- free, no sign-up
- Explanations
- answers and explanations for each question, available to expand
Three areas, five problems
What INDEX/MATCH fixes that VLOOKUP cannot
2 problemsTwo structural limits, not preferences. VLOOKUP can only return columns to the right of the lookup column, and its column index is a number counted inside the range — so inserting a column silently shifts what it returns without changing the formula or raising an error. INDEX/MATCH has neither problem: it points at the return column directly, so it can look left and it survives insertions. That second point is the one that matters on a shared workbook.
How it shows up: "INDEX MATCH vs VLOOKUP" · "Why do people say INDEX MATCH is better?"
Q1
This formula works today. A colleague then inserts a new column between B and C. What happens?
=VLOOKUP(F2, A:D, 3, FALSE)
- AIt breaks with #REF! so you notice immediately
- BIt keeps working but now returns the newly inserted column's data instead of the one you wanted — silently
- CExcel updates the index to 4 automatically
- DNothing changes; the index refers to the sheet, not the range
▶Show answer & explanation
Answer: B. It keeps working but now returns the newly inserted column's data instead of the one you wanted — silently
🐱 The column index is a fixed number, so after the insertion position 3 within A:D points at different data. No error appears, which is precisely what makes it dangerous on a workbook other people edit. INDEX(C:C, MATCH(F2, A:A, 0)) refers to column C directly, so an inserted column shifts the reference along with the data and the formula keeps meaning what you meant.
Q2
You need to look up an ID in column C and return the name from column A. Which works?
=VLOOKUP(F2, A:D, 1, FALSE)
=INDEX(A:A, MATCH(F2, C:C, 0))
- AThe first
- BThe second — VLOOKUP cannot return a column to the left of the one it searches
- CBoth work
- DNeither; you need XLOOKUP for this
▶Show answer & explanation
Answer: B. The second — VLOOKUP cannot return a column to the left of the one it searches
🐱 VLOOKUP searches the leftmost column of the range it is given, so with A:D it searches names, not IDs, and returns #N/A. INDEX/MATCH separates the two jobs — MATCH finds the row in column C, INDEX pulls that row from column A — and direction stops mattering. XLOOKUP also handles this, but INDEX/MATCH works in every Excel version, which is why it is still worth knowing.
The third argument of MATCH, and why it must be 0
1 problemMATCH's third argument works exactly like VLOOKUP's fourth, and it has the same trap: leave it out and it defaults to 1, meaning approximate match on data assumed to be sorted ascending. On unsorted data that returns a plausible wrong row rather than an error. Write 0 every time unless you are deliberately doing a banded lookup — this single habit prevents the most common INDEX/MATCH bug.
How it shows up: "INDEX MATCH returns the wrong row" · "What is match_type?"
Q3
The ID column is not sorted. What is wrong with this formula?
=INDEX(C:C, MATCH(F2, A:A))
- AINDEX needs three arguments
- BMATCH's third argument is missing, so it defaults to approximate match and can return the wrong row without any error
- CWhole-column references are not allowed in MATCH
- DNothing is wrong
▶Show answer & explanation
Answer: B. MATCH's third argument is missing, so it defaults to approximate match and can return the wrong row without any error
🐱 Omitting match_type is the same class of mistake as omitting VLOOKUP's FALSE, and it fails the same way — quietly. Write MATCH(F2, A:A, 0) for exact matching. If you ever do want a banded lookup (tax brackets, shipping tiers), match_type 1 is the right tool, but then the lookup column genuinely must be sorted ascending, and saying so out loud is what separates a deliberate choice from an accident.
XLOOKUP: when to switch and what it costs
2 problemsXLOOKUP removes all three traps at once: it defaults to exact match, it looks in any direction, and it references the return array directly so insertions do not break it. It also takes a built-in if-not-found argument, which replaces the IFERROR wrapper that hides other errors. The one real cost is compatibility — it needs Microsoft 365 or Excel 2021 and later, so a workbook shared with someone on an older version will show #NAME?.
How it shows up: "XLOOKUP vs VLOOKUP" · "Should I switch to XLOOKUP?"
Q4
You rewrite a lookup with XLOOKUP and send the workbook to a colleague on Excel 2019. What do they see?
=XLOOKUP(F2, C:C, A:A, "not found")
- AThe formula works normally
- B#NAME?, because their version does not have the XLOOKUP function
- C#VALUE!
- DExcel converts it to VLOOKUP automatically
▶Show answer & explanation
Answer: B. #NAME?, because their version does not have the XLOOKUP function
🐱 #NAME? is Excel's way of saying it does not recognise a function name — the same error you get from a typo. There is no automatic downgrade, and the formula does not merely fail to update: it shows an error in place of your data. On a workbook that circulates, INDEX/MATCH remains the safe choice; for your own files, XLOOKUP is strictly better and worth the switch.
Q5
Which of these does XLOOKUP handle without any extra wrapping?
- AReturning a custom message when nothing is found
- BSumming the matched rows
- CLooking up across multiple workbooks that are closed
- DMatching on cell colour
▶Show answer & explanation
Answer: A. Returning a custom message when nothing is found
🐱 The fourth argument is if_not_found, so XLOOKUP(F2, C:C, A:A, "not found") replaces the usual IFERROR wrapper — and does so more safely, because it handles only the not-found case and leaves genuine errors such as #REF! visible. Summing needs SUMIF/SUMIFS, closed-workbook lookups have their own constraints regardless of function, and no lookup function reads formatting.