XLOOKUP

Arguments

• lookup_value - The value to search for.
• lookup_array - The array or range to search.
• return_array - The array or range to return a value from.
• if_not_found (optional) - Value to return when no match is found (avoids #N/A).
• match_mode (optional) - 0 exact (default), -1/1 next smaller/larger, 2 wildcard.
• search_mode (optional) - 1 first-to-last (default), -1 last-to-first.

Examples

How XLOOKUP evaluates

XLOOKUP takes a lookup_array and a return_array as two separate arguments rather than one block plus a column number. Because the two arrays are independent, the return column can sit anywhere - left of the search column, on another sheet, or as a whole block of columns returned at once. Nothing has to be counted, and inserting a column between the two arrays does not break the formula.

Its defaults are the opposite of VLOOKUP's, and that is the main reason to prefer it. match_mode defaults to 0, exact match, so an unsorted list is safe out of the box. search_mode defaults to 1, first to last; pass -1 to search bottom-up, which is how you get the most recent row of a transaction log. The optional fourth argument, if_not_found, replaces the whole IFERROR wrapper - and unlike IFERROR it catches only the miss, leaving genuine errors in the data visible.

The cost is availability. XLOOKUP arrived with Microsoft 365 and Excel 2021; it does not exist in 2019 or earlier and a workbook using it opens in those versions showing #NAME? with the function name preserved as _xlfn.XLOOKUP. If the file will be opened by anyone on a perpetual licence older than 2021, INDEX/MATCH is the portable form of the same idea.

Arrays must be the same length

lookup_array and return_array are matched position by position. If one is 100 rows and the other 99, Excel returns #VALUE! rather than guessing an alignment. return_array may be wider than one column; the whole matching row comes back.

Returning a block spills

When return_array spans several columns, XLOOKUP returns the entire matching row as a dynamic array and it spills into the cells to the right. Those cells must be empty or you get #SPILL!.

Exact by default, approximate on request

match_mode 0 is exact. -1 returns the next smaller item when there is no exact hit, 1 the next larger, and 2 enables * and ? wildcards. Only match modes -1 and 1 have any interest in sort order, and even then XLOOKUP does not require the array to be sorted unless search_mode is set to a binary mode.

Binary search modes assume a sort

search_mode 2 and -2 run a binary search over data you promise is sorted ascending or descending. They are fast on very large arrays and return nonsense on unsorted data, with no warning.

if_not_found only catches the miss

The fourth argument fires when nothing matched. If the lookup_array itself contains an error, or the lookup value is an error, that error propagates instead - which is the behaviour you want, and what makes if_not_found safer than wrapping the call in IFERROR.

Blank cells match empty text

An empty cell in lookup_array is treated as matching an empty lookup value, so a blank key can find a blank row. Guard with an IF on the lookup value if blanks should return nothing.

Errors XLOOKUP returns

More examples

Compatibility

Does XLOOKUP Work in Google Sheets?

Yes. XLOOKUP uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.

In the ExcelTool editor

The ExcelTool editor accepts XLOOKUP with three to five arguments: lookup_value, lookup_array, return_array, if_not_found and match_mode. The sixth argument, search_mode, is not supported, so searches always run first to last.

match_mode 0 and an omitted match_mode are exact; -1 falls back to the largest key at or below the lookup value and 1 to the first key at or above it, each only after an exact scan has failed. Text comparison is case-insensitive, and a lookup value containing * or ? is always treated as a wildcard pattern rather than only under match_mode 2.

Orientation is inferred from the shape of lookup_array: taller than it is wide means a column search, otherwise a row search. When return_array is wider than the match, the whole matching row is returned as an array and spills into the neighbouring cells, or reports #SPILL! if they are occupied.

Omitting if_not_found gives #N/A, as in Excel. Unlike Excel, the editor does not reject a return_array that is a different length from lookup_array; a position past the end of the return array comes back as #N/A instead of #VALUE!.

You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.