XLOOKUP in Excel: Syntax, 6 Arguments, Errors and Example

The XLOOKUP formula: 3 required arguments and 3 optional ones

Microsoft gives the syntax as =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The first 3 arguments are required. The 3 in square brackets are optional.

XLOOKUP searches a range or an array and returns the item for the first match it finds. Because you point at the return column yourself, one formula can return a single cell or several columns at once.

• lookup_value: the value to search for. If you leave it out, XLOOKUP returns blank cells it finds in lookup_array.
• lookup_array: the range or array to search.
• return_array: the range or array to return.
• if_not_found: the text to show when there is no valid match. If you leave it out, Excel shows #N/A.
• match_mode: how to match (the 4 values are in the next section).
• search_mode: which direction or method to search (4 values, also below).

match_mode 0, -1, 1, 2 and search_mode 1, -1, 2, -2

match_mode 0 is the default: an exact match, and #N/A if there is none. Mode -1 returns an exact match or the next smaller item. Mode 1 returns an exact match or the next larger item. Mode 2 is a wildcard match, where the characters *, ? and ~ have special meaning.

search_mode 1 is the default and searches from the first item. Mode -1 searches in reverse from the last item, which is useful when a value appears twice and you want the latest row. Modes 2 and -2 are binary searches for data sorted ascending or descending. Microsoft warns that if the data is not sorted, invalid results are returned.

• 0 - exact match (default)
• -1 - exact, or next smaller item
• 1 - exact, or next larger item
• 2 - wildcard match
• Search: 1 first to last (default), -1 last to first, 2 binary ascending, -2 binary descending

XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365

Microsoft's XLOOKUP page says the function is not available in Excel 2016 and Excel 2019. The same page lists those two versions under "Applies To", so the sources disagree. The note is the more specific statement, so plan on Excel 2021, Excel 2024 or Microsoft 365 (Windows and Mac). The page also lists Excel for iPad, iPhone and Android.

A workbook made in a newer version may still contain XLOOKUP when it is opened in Excel 2016 or 2019. If you share files with people on those versions, test one before sending 100 rows of formulas.

The companion function XMATCH is narrower still. Its page lists only Microsoft 365, Excel 2024 and Excel 2021. XMATCH takes 4 arguments and returns a position instead of a value, with the same match_mode and search_mode numbers.

How XLOOKUP differs from VLOOKUP: 4 changes that matter

Microsoft describes XLOOKUP as an improved version of VLOOKUP that works in any direction and returns exact matches by default. VLOOKUP has 4 arguments, with col_index_num counting columns from 1. Its lookup value must sit in the first column of the range, so it can only return columns to the right.

XLOOKUP removes the column counting. Microsoft's own pair of equivalent formulas is =XLOOKUP(F2,B2:B11,D2:D11) and =VLOOKUP(F2,B2:D11,3,FALSE). If you insert a column in the VLOOKUP table the 3 can point at the wrong data. The XLOOKUP ranges move with the sheet.

VLOOKUP uses approximate match when range_lookup is left out, and you need 0 or FALSE for an exact one. XLOOKUP is exact until you change match_mode. VLOOKUP also returns #REF! when col_index_num is larger than the number of columns in table_array. XLOOKUP has no such argument.

• Left lookups: lookup column can be to the right of the return column.
• Exact match is the default, not approximate.
• if_not_found replaces wrapping the formula in an error function.
• Several columns can be returned in one formula.

A worked example with a 5-row product list

Say A2:A6 holds item IDs 101, 102, 103, 104 and 105. B2:B6 holds Mug, Lamp, Desk, Chair and Shelf. C2:C6 holds the prices 8.50, 24.00, 140.00, 85.00 and 60.00. These are sample values for practice, not from Microsoft.

With 104 in F2, =XLOOKUP(F2,A2:A6,B2:B6) returns Chair. To go the other way with Lamp typed in G2, =XLOOKUP(G2,B2:B6,A2:A6) returns 102. This is the left lookup: the ID column sits left of the name column and the formula still works.

To get name and price together, use =XLOOKUP(F2,A2:A6,B2:C6). It returns Chair in the formula cell and 85.00 in the cell to its right. For an ID that does not exist, =XLOOKUP(999,A2:A6,B2:B6,"Not listed") shows Not listed instead of #N/A.

A wildcard lookup sets the 5th argument to 2: =XLOOKUP("Ch*",B2:B6,A2:A6,"Not listed",2) returns 104, because Chair is the only product starting with Ch. The * stands for any run of characters, and ? stands for exactly one.

Fixing #N/A: 4 causes Microsoft lists

XLOOKUP returns #N/A when it finds no valid match and if_not_found is omitted. Microsoft's #N/A page names 4 causes you can check on a lookup. The first is that the value is simply not in the table, like a Banana missing from a fruit list.

The second is a data type mismatch. If the lookup value is a number and the table stores numbers as text, the two never match. Press Ctrl+1 to check cell formats. For a whole column, apply the format, then use Data > Text to Columns > Finish.

The third is stray spaces. Leading or trailing spaces block a match, and Microsoft suggests wrapping the range in TRIM. The fourth is mismatched ranges: lookup_array and return_array should cover the same rows, such as B2:B11 with D2:D11.

To hide the error on purpose, use the if_not_found argument or IFNA(value, value_if_na), which takes 2 required arguments and is listed for Excel 2016 and later. Fix the cause first, because a hidden #N/A looks like a clean result.

Fixing #SPILL!: 7 causes, most often a blocked range

A formula that returns several values, such as the 2-column example above, fills the cells next to it. This is called spilling. If something blocks that output range, Excel shows #SPILL!. Select the error cell to see a dashed border around the intended range, then choose Select Obstructing Cells to find what is in the way.

Microsoft's #SPILL! page lists 7 causes: a spill range that is not blank, an indeterminate size, a result that runs past the edge of the sheet, a formula inside a table, running out of memory, merged cells, and an unrecognized cause. Spilled formulas are not supported inside Excel tables, so move the formula outside or convert the table to a range.

The edge-of-sheet case happens with whole-column references. Microsoft's example is a VLOOKUP over A:A, which tries to return 1,048,576 results. Point the lookup_value at the one cell you need, such as F2, and the problem goes away.

Only the top-left cell of a spill range is editable. To change the formula, edit that cell and press Enter.

Who should use XLOOKUP and who should keep VLOOKUP

XLOOKUP suits anyone on Microsoft 365, Excel 2021 or Excel 2024 who looks things up by ID, name or code. It helps most when the key column is not the first one, when you need 2 or more columns back, or when you want a friendly message instead of #N/A.

It does not suit workbooks that must open in Excel 2016 or 2019, where Microsoft says the function is not available. In that case INDEX and MATCH, or a VLOOKUP with FALSE as the 4th argument, still work.

If you are looking at an error right now, Crade can read your screen and explain which cell is causing it.