INDEX MATCH vs VLOOKUP vs XLOOKUP: 5 Problems That Show the Difference

Five problems that show why people move off VLOOKUP — and what still breaks after they do. Each group opens with the structural reason, then gives you a formula to judge.

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.

Three areas, five problems

What INDEX/MATCH fixes that VLOOKUP cannot

2 problems

Two 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?"

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.

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 problem

MATCH'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?"

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 problems

XLOOKUP 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?"

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.

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.

Keep going

Excel formulas practice (8 problems)Why VLOOKUP returns #N/A, dollar signs and drag-fill, reading error values.SUMIF and COUNTIF practiceThe other silent-zero family: argument order, criteria strings and wildcards.SQL interview questionsJoins are the database version of a lookup — same idea, different failure modes.

INDEX MATCH, VLOOKUP and XLOOKUP — FAQ

Is INDEX MATCH really better than VLOOKUP?

For two specific reasons, yes: it can return columns to the left, and it survives column insertion because it references the return column directly rather than by a counted index. For a small, stable sheet that nobody else edits, VLOOKUP with FALSE is perfectly fine.

Should I switch everything to XLOOKUP?

In your own files, yes — it defaults to exact match, works in any direction, and has a built-in not-found argument. For workbooks you send to others, check their Excel version first: anything before Microsoft 365 or Excel 2021 shows #NAME?.

Why does my INDEX MATCH still return the wrong row?

Almost always the missing third argument of MATCH. Without it, MATCH defaults to approximate matching and assumes the lookup column is sorted ascending — so on unsorted data it returns a plausible wrong row rather than an error. Write 0.

Which is faster on large sheets?

The differences are small compared with structural choices such as referencing whole columns versus a bounded range, or leaving volatile functions in the workbook. Choose on correctness and maintainability first; if performance genuinely matters, limit the ranges before switching functions.

Can any of them look up more than one criterion?

XLOOKUP and INDEX/MATCH can, by concatenating criteria or using a boolean multiplication inside MATCH. It is worth learning after the basics here are automatic — most multi-criteria bugs are really single-criteria bugs that were never diagnosed.