XLOOKUP Not Working? Causes and Fixes

When XLOOKUP is not working, the error it shows tells you most of what you need to know. #NAME? points to your Excel version, #N/A to the data, and #VALUE! to the ranges. This guide goes through each error and the quieter problems, like wildcards that don't match and duplicates that return the wrong row, with a fix for every one.

XLOOKUP errors at a glance#

Start with the error in the cell. Each one has a small set of causes:

The full syntax, for reference:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The examples use this product list in A1:C7:

=XLOOKUP("B-200",A2:A7,C2:C7) returns 45. The guide to using XLOOKUP covers the basics.

#NAME?: your Excel doesn't have XLOOKUP#

XLOOKUP is available in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Excel 2019, 2016 and older don't have it. If you open a file with XLOOKUP in an older version, the formula shows #NAME?, and the formula bar may show _xlfn.XLOOKUP.

Check your version under File > Account (Mac: Excel > About Excel). If people who share the file use older Excel, use INDEX MATCH instead, which works everywhere:

=INDEX(C2:C7,MATCH("B-200",A2:A7,0))

If your version is new enough, #NAME? usually means a typo, such as XLOOKUP spelled wrong or text without quotes. The INDEX MATCH vs XLOOKUP comparison helps you choose.

#N/A: the value wasn't found#

#N/A means XLOOKUP looked through the whole lookup array and found no exact match. Sometimes that's correct: the code really isn't there. Often, though, the value is there but doesn't match exactly:

• Extra spaces. "B-200 " with a trailing space doesn't match "B-200". Check with =LEN(A4), and clean the column with TRIM.
• Text vs numbers. The number 1004 and the text "1004" are different. Convert the column with Convert to Number, or adjust the lookup value: E2&"" makes it text and VALUE(E2) makes it a number.
• Typos and look-alike characters, such as a letter O instead of a zero.

These causes are the same for every lookup function. See VLOOKUP not working and INDEX MATCH not working for more examples.

How to show a message instead of #N/A#

XLOOKUP has a built-in fourth argument, if_not_found, for values that are genuinely missing:

=XLOOKUP("D-400",A2:A7,C2:C7,"Not found")

There's no code D-400, so the result is Not found instead of #N/A. You can return text, "" for a blank, or a number such as 0. This is better than wrapping the formula in IFERROR, because it only handles "not found" and still shows real errors like #VALUE!. The article on XLOOKUP if not found has more patterns.

#VALUE!: the ranges are different sizes#

The lookup array and the return array must have the same number of rows (or columns, for a horizontal lookup). If they don't, XLOOKUP returns #VALUE!:

=XLOOKUP("B-200",A2:A7,C2:C6)

A2:A7 has six rows and C2:C6 has five. Fix it to C2:C7. This often happens after you add rows and only update one range. Turning the data into an Excel table (Windows: Ctrl+T; Mac: ⌘T) and using its column names keeps both ranges the same size automatically.

#SPILL!: the result has no room#

XLOOKUP can return more than one cell. If the return array has several columns, such as B2:C7, it returns both Product and Price side by side. If you give it several lookup values, such as E2:E4, it returns several results down the column. Those results spill into neighbouring cells, and if any of them already hold something, you see #SPILL!.

Clear the cells the result needs, or return a single column. Also avoid whole-column references like A:A as the lookup value: that asks for over a million results.

Wrong value: wildcards, match mode and duplicates#

Sometimes XLOOKUP returns a value, just not the one you wanted.

Wildcards need match_mode 2. By default, * and ? are normal characters, so "C*" looks for the literal text C*. Set the fifth argument to 2:

=XLOOKUP("C*",A2:A7,B2:B7,,2)

This returns Monitor, the first code starting with C. The two commas leave if_not_found empty.

Approximate match needs match_mode -1 or 1. For tier tables like tax bands, -1 returns the next smaller value and 1 the next larger. Leave it out and XLOOKUP only does exact matches, so a value between tiers gives #N/A.

Duplicates return the first match. If a code appears twice, XLOOKUP returns the first one from the top. Set the sixth argument, search_mode, to -1 to search from the bottom and get the last match. Don't use search_mode 2 or -2 (binary search) unless the data is sorted, because on unsorted data it can return wrong results.

The XLOOKUP function guide lists every match_mode and search_mode value.

Practise it#

Practise XLOOKUP in a live spreadsheet: you'll write exact lookups, add an if_not_found message and look to the left, with each formula checked instantly. Then fix broken formulas in the lookup errors lesson to practise spotting #N/A and #VALUE! causes. Start with the exercise on this page.