XLOOKUP vs VLOOKUP vs INDEX/MATCH: Which to Use When

Lookups are the most-used non-trivial thing people do in spreadsheets, and the most common source of numbers that are wrong without looking wrong. Which function you use matters less than two decisions sitting underneath it: exact or approximate matching, and what to return when nothing matches.

The short version

VLOOKUP: the one everybody knows

It reads left to right and everyone learned it first, which is why it won't die. Two things make it genuinely risky in a shared workbook.

The other problem is that 4 means "the fourth column of the range", counted by position. Someone inserts a column inside that range on a Tuesday afternoon, without a thought, and every VLOOKUP pointing at it now returns a different field. Still no error. That's the part that hurts.

INDEX/MATCH: the one that keeps working

Two functions instead of one, and it fixes both VLOOKUP problems. The return column is referenced directly rather than counted, so inserting columns doesn't break it. The search range and the return range are independent, so it looks in any direction. And it works in every version of Excel and Google Sheets ever shipped, which is why it's still the right answer for files that circulate widely.

The 0 in MATCH is the exact-match flag and carries the same warning as VLOOKUP's FALSE. Leave it out and you're back to approximate matching on data you almost certainly haven't sorted.

XLOOKUP: the one to use if you can

The arguments come in the order you'd say them out loud. Exact match is the default, so the most dangerous mistake in the older functions simply isn't available. And the fourth argument replaces the IFERROR wrapper, which is an improvement, because IFERROR swallows every error including the #REF! that was trying to tell you something was properly broken.

Two extras are worth knowing. The fifth argument turns on wildcard and approximate modes when you actually want them, and the sixth searches bottom-up, which is the clean way to get the most recent matching record instead of the first.

Choosing, in one question

Who opens this file? If it's only people on current Microsoft 365 or Google Sheets, use XLOOKUP and stop thinking about it. If anyone might be on an older Excel, or you honestly don't know, use INDEX/MATCH. Use VLOOKUP when you're editing a sheet that's already full of it and consistency beats the upgrade.

The mistakes all three share

One side is text, the other is a number

This is behind most of the "it returns #N/A but I can see the value right there" complaints. Order IDs imported from CSV are the usual suspect. Check both sides with =ISNUMBER(), and if they disagree, fix the data rather than coercing inside the formula. The cleaning checklist has the repair.

Invisible whitespace

"ACME" and "ACME " are not the same string. Run TRIM over both key columns before you conclude a record is missing. If the value came off a web page, TRIM won't even get it, because a non-breaking space isn't a space as far as Excel is concerned.

Keys that repeat

Every lookup returns exactly one match: the first one it finds, or the last if you asked XLOOKUP to search backwards. If your key isn't unique the function isn't wrong, your assumption is. Count with =COUNTIF(A:A, B2) first and decide whether you want the first, the last, or all of them added up. If it's all of them, you wanted SUMIFS from the start, and an AI formula generator will pick that up if you describe the result instead of the function.

Ranges that stop too early

Products!A2:A500 in a sheet that's grown to 800 rows gives you #N/A for everything past row 500. Use whole-column references, or convert the source to a table so the range grows with the data. The function reference has the full argument list for each of these.

Handle the not-found case on purpose

An #N/A is information. It means this key isn't in the reference data. Wrapping every lookup in IFERROR(..., "") throws that information away and turns a visible gap into an empty cell that flows into a total, which is how a missing 4% of revenue becomes nobody's problem.

• Leave #N/A visible while you build. It's how you discover 40 SKUs are missing from the product master.
• Swap in a readable label like "Not listed" before anyone else sees the sheet.
• Use zero only when zero is genuinely correct, not to make a chart look tidy.
• Prefer IFNA to IFERROR. It catches the no-match case and lets real errors surface.