XLOOKUP in Excel

XLOOKUP in Excel searches a specified column for a value and returns a corresponding value from a separate column, without requiring the two columns to be part of a single combined range. It was introduced to address several long-standing limitations of VLOOKUP.

This page covers how the formula works, a complete worked example with an order table, and the practical differences from VLOOKUP worth knowing.

1. Excel scans lookup_array for a value matching lookup_value.
2. Once a matching position is found, Excel notes its row position within that array.
3. It then looks at the same relative row position within return_array.
4. The value at that position in return_array is returned as the result.

This order table is used to find the Status of ORD-501:

XLOOKUP doesn't require the lookup column to be leftmost - return_array can sit anywhere relative to lookup_array, including to its left. It also defaults to an exact match, unlike VLOOKUP which defaults to an approximate one, making XLOOKUP arguably safer for beginners.

Selecting a multi-column return_array, such as B2:D4, returns all three columns at once when the formula is entered as a spilling array formula, which VLOOKUP cannot do in a single formula.

Turbo Excel VLookup applies this same matching logic across an entire order list at once - you choose the matching columns and the columns to return through dropdown menus, without writing or copying the formula manually.

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.