XLOOKUP vs VLOOKUP: which should you actually use?

If you have to pull a value out of a table in Excel, your reflex might be VLOOKUP. It has been the default for years, it still works, and you probably have a few old workbooks that depend on it. It is also the formula most people learn first when they move from simple totals to real analysis.

But XLOOKUP exists now, and it solves the biggest pain points in VLOOKUP. The result is a function that is easier to read, less fragile, and more forgiving when data changes. You can usually build a cleaner worksheet with fewer helper columns and fewer formulas that only you understand.

This guide shows where each function wins, where VLOOKUP still makes sense, and how to decide quickly. Think of it as a practical checklist rather than a feature list.

Quick syntax snapshot

VLOOKUP uses a single table range and a column number. The column number is the part that often breaks when someone inserts or deletes a column.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

XLOOKUP splits the lookup and return ranges, which makes the formula easier to read and safer when a table changes shape.

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

If you need a quick refresher, see the function pages for VLOOKUP and XLOOKUP.

Here is how the arguments map in plain English:

• VLOOKUP: “Find this value in the first column of my table, then return the value from column number X.”
• XLOOKUP: “Find this value in this column, then return the value from that other column.”

That sounds subtle, but it changes how you build tables. With XLOOKUP, you can keep your data in a natural order and still return the right value.

If you are new to XLOOKUP, here is a quick guide to the extra arguments:

• if_not_found: what to return instead of #N/A.
• match_mode: exact match (0, default), nearest smaller (-1), nearest larger (1), or wildcard (2).
• search_mode: search top-to-bottom (1, default) or bottom-to-top (-1).

You can ignore those until you need them. The core three arguments are enough for most day-to-day work.

Basic lookup: same task, cleaner formula

Both functions can do a simple lookup by ID. Here is the same dataset with a standard VLOOKUP.

Now the same task with XLOOKUP. Notice how the ranges make it obvious which column is searched and which column is returned.

In practice, that readability matters. A month from now, you or someone else should be able to open the file and understand it without guessing what column number 2 means. If a column gets inserted or removed, an XLOOKUP that uses explicit ranges keeps working. A VLOOKUP that uses a column number is more likely to break silently.

If you have to keep VLOOKUP, the safest pattern is to always include the 4th argument set to FALSE for exact match. It makes the intent obvious and prevents the default approximate match from surprising you later.

One more practical tip: exact matches are sensitive to data cleanliness. Extra spaces, numbers stored as text, or mismatched formats can cause a lookup to fail. When a lookup looks correct but still returns #N/A, check for hidden spaces with TRIM, or convert text numbers with VALUE.

Left lookup: where VLOOKUP breaks

VLOOKUP can only return values from columns to the right of your lookup value. If the value you need is to the left, VLOOKUP cannot do it.

XLOOKUP has no such limitation. You can look up a product name in column B and return the SKU from column A without rearranging your data.

Try it in this exercise:

This is also the moment where people often learn INDEX + MATCH. That combo is still useful, especially in older Excel versions. If you want the classic workaround, see use INDEX and MATCH to replace VLOOKUP.

If you are curious, XLOOKUP is effectively the same concept as INDEX + MATCH packaged into one function: one range to search, one range to return.

Default match behavior: the hidden gotcha

VLOOKUP has a risky default. If you omit the 4th argument, it assumes approximate match (TRUE). That can return a value that looks correct but is actually from the wrong row.

Approximate match only makes sense when your lookup column is sorted and you are intentionally looking for the nearest lower value (like tax brackets, tiered pricing, or commission rates). If the data is not sorted, approximate match can return unpredictable results.

XLOOKUP defaults to exact match. If there is no exact match, it returns #N/A unless you provide a fallback value.

Here is a quick example. The lookup score is 67. VLOOKUP returns the 60-point grade because it defaults to approximate match. XLOOKUP returns a clear “No exact match” message.

If you stick with VLOOKUP, make it a habit to add FALSE for exact matches:

VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

If you do want approximate match, XLOOKUP supports it explicitly. Use a match mode of -1 or 1 to find the nearest smaller or larger value:

XLOOKUP(lookup_value, lookup_array, return_array, "Not found", -1)

That keeps the intent readable and prevents accidental approximate matches.

Error handling: clean results without #N/A

Missing data is normal. Reports and dashboards look better when your formulas return a friendly message instead of #N/A. It also makes downstream formulas easier to debug because you can clearly see where the lookup failed.

With VLOOKUP, that usually means wrapping it with IFNA (or IFERROR in older versions).

XLOOKUP includes a built-in not_found argument, so you can handle errors without nesting another function.

Practice both approaches here:

If your output should stay numeric, use a numeric fallback instead of text. For example, return 0 or a blank string so charts and totals continue to work.

Maintenance and performance

VLOOKUP still works, and in small files you probably will not notice a performance difference. The real win with XLOOKUP is maintenance:

• You do not have to count columns.
• You can insert or move columns without breaking formulas.
• You can set a clean default when nothing is found.

XLOOKUP also lets you search from the bottom of a list or return the last match by using its search_mode argument. That is a common request in reporting where you want the most recent entry rather than the first.

Both functions get even easier to maintain if your data is in an Excel Table. Table headers give you a clear structure, and you can use structured references to keep formulas readable. That said, XLOOKUP still reads more naturally because you can point directly at the specific columns you want.

If you are maintaining older workbooks, VLOOKUP is still fine. If you are building new ones and your Excel version supports XLOOKUP, it is usually the better choice.

So which should you use?

Use VLOOKUP when you need maximum backward compatibility or you are editing an old template that already uses it. Use XLOOKUP for everything else.

Quick decision list:

• Use VLOOKUP if the workbook has to open in older Excel versions that do not include XLOOKUP.
• Use XLOOKUP if you want formulas that survive column inserts and are easier to read later.
• Use XLOOKUP when you need left lookups, exact defaults, or cleaner error handling.

If you are unsure, start new models with XLOOKUP and keep VLOOKUP only where compatibility forces it. That single choice usually saves a lot of cleanup later.

Want more hands-on practice? Try a few guided exercises on our Excel exercises page.