XLOOKUP in Excel: how to use it, and why it beats VLOOKUP

XLOOKUP is Excel’s modern lookup function, and for most new formulas it is the one to reach for instead of VLOOKUP. It searches one column or row for a value and returns the matching value from another, in either direction. It matches exactly by default, and it takes a built-in argument for what to show when there is no match.

The three required parts are the value to find, the array to search, and the array to return: =XLOOKUP(lookup_value, lookup_array, return_array). Everything else is optional. The rest of this guide walks through each argument, shows why XLOOKUP is safer than VLOOKUP, and covers the errors that trip people up.

What is XLOOKUP?

XLOOKUP finds a value in a lookup array and returns the value in the same position from a return array. The full syntax is:

The first three arguments are required; the three in brackets are optional. Here it looks up an employee ID and returns that person’s department:

If the value isn’t found and you haven’t told it otherwise, XLOOKUP returns #N/A.

How to use XLOOKUP

Read the three required arguments as a sentence: find this, in here, and give me back that.

• lookup_value is what you’re searching for, a value or a cell reference.
• lookup_array is the single column or row that holds it.
• return_array is the column or row you want the answer from.

The lookup and return arrays should be the same length, and they can sit in any order. That last point is the first big difference from VLOOKUP: the column you search can be to the right of the column you return.

This searches the Dept column (C) and returns the ID (A), to its left. VLOOKUP cannot do that at all, because it only ever looks in the leftmost column of its table.

XLOOKUP vs VLOOKUP: what actually changed

VLOOKUP still works, and on a simple left-to-right lookup the two behave the same. But XLOOKUP removes the traps that make VLOOKUP fragile:

Two of those rows matter most in practice. First, XLOOKUP matches exactly by default, while VLOOKUP defaults to an approximate match that quietly returns the wrong row on unsorted data. Second, XLOOKUP returns an array instead of a counted column number: a VLOOKUP written as =VLOOKUP(D2, A2:F5, 4, FALSE) breaks the moment someone inserts a column, because column 4 is now the wrong one. XLOOKUP points at the return column directly, so it moves with it.

The row-by-row trade-offs, including when VLOOKUP is still the right call, get a fuller treatment in VLOOKUP vs XLOOKUP: which should you use?

The optional arguments that make XLOOKUP powerful

if_not_found (4th argument) replaces the need to wrap the whole thing in IFNA. Instead of a bare #N/A, show your own text:

match_mode (5th argument) controls how it matches. The default is an exact match, which is what you want almost every time.

• 0 exact match (the default; returns #N/A if there’s no match)
• -1 exact match, or the next smaller item
• 1 exact match, or the next larger item
• 2 wildcard match, where *, ?, and ~ have special meaning

search_mode (6th argument) controls direction. It defaults to searching top-to-bottom (1). Use -1 to search bottom-to-top, which is how you find the last match rather than the first. Values 2 and -2 run a faster binary search but require the data to be sorted.

Return more than one column by giving return_array several columns. This single formula returns both the name and the department, spilling into two cells:

XLOOKUP with multiple criteria

Because lookup_array can be an expression rather than a plain range, XLOOKUP can match on more than one column. The most common pattern multiplies the conditions together and looks for the row where they’re all true (a 1):

If you’d rather not think in arrays, join the keys instead and look up the combined string:

Both return the value from C2:C5 on the row where the region and the quarter match.

Why is my XLOOKUP not working?

A few errors come up again and again:

• #N/A means no match was found. Confirm the value is really in the lookup array, that both sides are the same data type (the number 1005 will not match the text "1005"), and that there are no trailing spaces. To handle an expected miss gracefully, use the if_not_found argument. The same mismatches break VLOOKUP too, and why your VLOOKUP returns #N/A walks through each fix step by step.
• #SPILL! appears when XLOOKUP returns more than one cell but the cells where the result would land aren’t empty. Clear the cells below or to the right of the formula so it has room to spill.
• #NAME? usually means the workbook was opened in a version of Excel that doesn’t have XLOOKUP (see below).

Is XLOOKUP available in my Excel?

XLOOKUP is in Excel for Microsoft 365, Excel 2021, Excel 2024, and later. It is not in Excel 2016 or Excel 2019. If a workbook built with XLOOKUP is opened in one of those older versions, the formula shows a #NAME? error, so it’s worth knowing which version your colleagues run before you rely on it in a shared file. When you need a formula that works everywhere, INDEX and MATCH is the version-proof alternative that, like XLOOKUP, can look in any direction.

The short version

For a new lookup on current Excel, prefer XLOOKUP. Give it the value, the column to search, and the column to return; add an if_not_found message so a miss reads clearly; and leave the match and search modes on their exact, top-to-bottom defaults unless you have a reason not to. When you need where a value sits rather than the value itself, XMATCH is the same idea in position form. If you’re moving a shared model off VLOOKUP, compare the workbook before and after so you can confirm the numbers didn’t move when the formulas did.