Easy Excel XLOOKUP

XLOOKUP was designed to be easier than VLOOKUP, and for the most part it succeeds - there's no column counting, no leftward-search restriction, and no need to remember TRUE or FALSE. It only needs three arguments for a basic lookup: what to search for, where to search, and what to return.

This page shows the easy version of XLOOKUP using a small employee table, then covers Turbo Excel VLookup for anyone who'd rather not write a formula at all.

A basic XLOOKUP needs the value you're searching for, the column that contains it, and the column you want returned - notice there's no counting involved, since you point directly at the return column instead of counting over to it.

Using this employee table, to find the Department for E102:

lookup_array (A2:A4) and return_array (C2:C4) are pointed at directly, rather than counted from a single combined range.

Because lookup_array and return_array are selected independently, there's no risk of miscounting columns, and the return column doesn't need to sit to the right of the lookup column - it can be anywhere on the sheet, including to the left.

XLOOKUP has a built-in fourth argument for missing matches, so there's no need to wrap it in IFERROR:

Turbo Excel VLookup applies this same easy matching logic across an entire dataset - load your files, pick the matching columns from dropdowns, and run the match, without writing XLOOKUP into any cell.

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.