XLOOKUP vs VLOOKUP in Google Sheets: Which to Use

XLOOKUP and VLOOKUP solve the same problem: find a value in one column, return the matching value from another. The difference is how much can go wrong along the way. VLOOKUP carries three famous traps, and XLOOKUP was designed specifically to remove them.

In This Guide

• The Same Lookup, Both Ways
• What XLOOKUP Fixes
• Feature Comparison
• Converting VLOOKUP to XLOOKUP
• When VLOOKUP Is Still Fine
• Related Google Sheets Tutorials

The Same Lookup, Both Ways

Here is a product table, with IDs in column A and prices in column D:

Looking up the price of SKU-103 with VLOOKUP:

And with XLOOKUP:

Both return $35.00. But notice what the XLOOKUP version does not need: no column counting to arrive at 4, and no FALSE at the end. The two ranges say exactly what they mean: search the IDs, return the prices.

What XLOOKUP Fixes

1. Exact match is the default

VLOOKUP’s fourth argument defaults to TRUE, approximate match. Forget the FALSE and a missing value silently returns the wrong row instead of an error. This is the single most common VLOOKUP bug, and XLOOKUP removes it by defaulting to exact match. You add a match mode only when you want approximate behavior on purpose.

2. It can look left

VLOOKUP only returns columns to the right of the search column. Searching column B to return column A requires an INDEX MATCH workaround. XLOOKUP’s lookup and result ranges are independent, so a left lookup is just:

3. No fragile column index

VLOOKUP’s 4 means “the fourth column of the range I gave you.” Insert a column inside that range and every VLOOKUP pointing into it now returns the wrong field, with no error. XLOOKUP points at the result column directly, so inserting columns cannot silently redirect it.

4. Built-in error handling

A VLOOKUP that finds nothing returns #N/A, and cleaning that up means wrapping the whole formula in IFERROR. XLOOKUP has a dedicated fourth argument for it:

See XLOOKUP if not found for why this beats IFERROR.

5. One formula can return several columns

Point the result range at multiple columns and XLOOKUP spills all of them from a single formula. VLOOKUP returns one column per formula, full stop.

Feature Comparison

Converting VLOOKUP to XLOOKUP

The conversion is mechanical. Take the VLOOKUP’s table range, split it into the column you search and the column you return:

The last row deserves a note: VLOOKUP’s TRUE finds the closest value at or below the search key, which is XLOOKUP’s match_mode -1. One real difference: VLOOKUP’s TRUE requires sorted data, while XLOOKUP’s -1 scans the whole range and works unsorted. Details in match modes.

When VLOOKUP Is Still Fine

There are exactly two good reasons to keep writing VLOOKUP:

Compatibility. If your sheet gets exported to Excel and opened in Excel 2019 or earlier, XLOOKUP formulas will not work there. Teams stuck on old Excel are the one audience where VLOOKUP remains the safe choice.

Working formulas. A VLOOKUP that already does its job costs nothing to keep. Rewriting working formulas adds risk without adding value. The rule is simple: new formulas get XLOOKUP, old formulas get left alone.

If you are choosing between XLOOKUP and the classic workaround pattern instead, see XLOOKUP vs INDEX MATCH.

Related Google Sheets Tutorials

• XLOOKUP: The Complete Guide: Full syntax and every parameter explained
• VLOOKUP: The Complete Guide: The original lookup function in depth
• XLOOKUP vs INDEX MATCH: The other alternative compared
• XLOOKUP Match Modes: Exact, approximate, and wildcard matching
• Fix VLOOKUP Errors: Troubleshooting the classic function