How to Use XLOOKUP Instead of VLOOKUP

Switching from VLOOKUP to XLOOKUP mostly comes down to splitting one combined table_array into two separate ranges - lookup_array and return_array - and dropping the column-counting step entirely. The underlying matching logic stays the same; only how the ranges are described changes.

This page walks through converting an existing VLOOKUP formula to XLOOKUP using an invoice table, and covers when making the switch is actually worth the effort.

A2:D4 splits into two separate ranges: the Invoice No column becomes lookup_array, and the Amount Due column becomes return_array directly, with no column counting needed.

XLOOKUP defaults to an exact match without needing FALSE typed explicitly, handles missing values with a built-in fallback argument instead of a separate IFERROR wrapper, and doesn't break if a column is inserted between the lookup and return columns, since they're referenced independently.

If the workbook needs to open correctly in Excel 2019 or earlier, or in older versions of Google Sheets, XLOOKUP isn't available and VLOOKUP remains the more compatible choice. Existing VLOOKUP formulas that already work reliably also don't necessarily need to be rewritten just because a newer function exists.

For anyone converting formulas mainly to make matching easier rather than for a specific XLOOKUP feature, Turbo Excel VLookup sidesteps the choice entirely, matching an entire dataset through dropdown menus regardless of which formula would otherwise have been used.

Because lookup_array and return_array are selected independently rather than counted from a combined range, inserting a new column between them doesn't shift col_index_num the way it would with VLOOKUP - Excel automatically adjusts both named ranges to keep pointing at the correct data.

This is one of the more practical advantages of XLOOKUP in a spreadsheet that's edited frequently, since it removes a whole category of silent, hard-to-spot errors.

Before copying an XLOOKUP formula down an entire column, testing it against a single known row and manually verifying the result catches setup mistakes - like mismatched range sizes - before they propagate across a whole dataset.

XLOOKUP's optional fifth argument, match_mode, defaults to an exact match, but it also supports approximate matches - either the next smaller or next larger value when no exact match exists - by passing -1 or 1 instead of the default 0.

This is useful for scenarios like matching a score against grade thresholds, where an exact match won't always exist but the closest applicable bracket still needs to be found.