VLOOKUP vs XLOOKUP, Which Should You Use?

If you've used Excel for a few years, you learned VLOOKUP first. If you're newer, you may have only heard of XLOOKUP. Both functions search for a value and return a matching result from another column, but they work differently enough that switching between them without understanding the difference causes real errors.

What each function actually needs

VLOOKUP's syntax, per Microsoft's documentation, is:

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

You give it one value to search for, a block of cells to search in, and a column number (counted from the left edge of that block) to pull the result from. The function only searches the first (leftmost) column of your range and can only return values from columns to the right of it.

XLOOKUP's syntax is:

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

Instead of one block of cells and a column number, you point to two separate ranges: the one you're searching in, and the one you want results from. Microsoft's documentation states this directly: "XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number." Because the two ranges are separate, the return range does not have to be to the right of the lookup range. It can be to the left.

A worked example

Take this small product table:

Column	A: Product	B: Price	C: Category
Row 4	Apple	1.50	Fruit
Row 5	Bread	3.20	Bakery
Row 6	Cheese	5.75	Dairy

Finding the price of "Cheese" with VLOOKUP:

=VLOOKUP("Cheese", A4:C6, 2, FALSE)

This returns 5.75. The 2 means "the second column of A4:C6," which is column B (Price). The FALSE tells VLOOKUP to require an exact match; Microsoft's documentation recommends always using FALSE for this reason, since the default behavior without it is an approximate match, which can silently return the wrong row if your data is not sorted.

The same lookup with XLOOKUP:

=XLOOKUP("Cheese", A4:A6, B4:B6)

This also returns 5.75. Notice the structure is different: you point directly to the Product column (A4:A6) as the search range and directly to the Price column (B4:B6) as the return range, instead of giving a column number inside a combined block.

Now suppose Category (column C) is to the left of Product, so the layout is Category, Price, Product instead. VLOOKUP cannot look up a Product and return a Price in that case if Product is the rightmost column and Price is to its left, because VLOOKUP can only return values to the right of its search column. XLOOKUP has no such restriction, since its two ranges are independent: =XLOOKUP("Cheese", C4:C6, B4:B6) works regardless of which range is physically left or right of the other on the sheet.

Where VLOOKUP still causes real problems

Microsoft's own VLOOKUP documentation lists several recurring issues:

• #N/A when no exact match exists, if you forgot the FALSE argument and the approximate match logic returns an unexpected row.
• Only the first match is returned. If your search key appears more than once in the data, VLOOKUP always returns the result tied to the first occurrence, silently ignoring any later matches, which can hide data entry duplicates.
• Unclean data causes mismatches. Leading or trailing spaces (" Apple" vs "Apple") are treated as different values.

XLOOKUP fixes one of these by default: its [if_not_found] argument lets you specify exactly what to show instead of #N/A, for example =XLOOKUP("Cheese", A4:A6, B4:B6, "Not found"), without wrapping the whole formula in a separate IFNA function the way VLOOKUP requires.

When VLOOKUP is still fine to use

If you're working in Excel 2016 or Excel 2019, Microsoft's documentation notes that XLOOKUP is not available in those versions at all, so VLOOKUP (or INDEX/MATCH) is your only option. If you're sharing a workbook with people on older Excel versions, using VLOOKUP keeps the file working for everyone who opens it, whereas a workbook built with XLOOKUP will show an error in Excel 2016 or 2019.

A simple rule for choosing

• Use VLOOKUP if you're on Excel 2016/2019, sharing the file with people who are, or the data you're searching is always to the left of the result you want.
• Use XLOOKUP for anything else: when the result column is to the left of the search column, when you want a built-in "not found" message, or when you're working only in modern Excel (2021, Microsoft 365) or Google Sheets, which also supports a form of XLOOKUP for its own lookup tables.

Key takeaways

• VLOOKUP needs one combined range plus a column number; XLOOKUP needs two separate ranges, which removes the "result must be to the right" restriction.
• Always add FALSE (or 0 for match_mode in XLOOKUP) to force an exact match; the default approximate-match behavior is a common source of silently wrong answers.
• VLOOKUP only returns the first matching row if your search key repeats; this is true of plain XLOOKUP too unless you adjust search_mode.
• XLOOKUP's if_not_found argument replaces the extra IFNA() wrapper that VLOOKUP typically needs.
• XLOOKUP does not exist in Excel 2016 or 2019, so check your audience's Excel version before switching a shared workbook over.