An XLOOKUP formula returns #N/A when it cannot find its lookup value in the lookup array, or when the matched result itself contains #N/A. Check the lookup value and ranges first; to display a friendlier message when no match exists, use XLOOKUP’s optional if_not_found argument.
What the XLOOKUP #N/A error means
XLOOKUP searches one range or array for a value and returns a corresponding value from another range or array. By default, it looks for an exact match. If it finds no match and you have not supplied an alternative, Excel returns #N/A. The error is a signal that the requested match was not located; it does not necessarily mean the formula is malformed.
For example, with a product code in E2, product codes in A2:A100, and prices in C2:C100, this formula returns the matching price. If E2 is not present in A2:A100, it returns #N/A.
With the lookup value in E2, lookup codes in A2:A100, and corresponding prices in C2:C100, use: =XLOOKUP(E2,A2:A100,C2:C100)
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel for Mac and Excel for the web. Excel 2019 and earlier do not include the function; entering it there typically produces #NAME?, a different error from #N/A.
Start with these checks
Before changing the formula, confirm that the value you want to find should genuinely appear in the selected lookup range. Then check that the formula points to the correct worksheet, column, and rows. A reference to A2:A50 cannot find a code that is actually in A51, even if the code is visible elsewhere in the workbook.
- Click the formula cell and inspect each argument in the formula bar. The second argument must be the range containing the search values; the third must contain the corresponding return values.
- Check that the lookup and return arrays cover the same number of rows or columns. If one range starts on row 2 and the other on row 3, results may be misaligned; incompatible dimensions can also cause a different error.
- Verify that the lookup value is not blank or coming from the wrong input cell. A formula referring to E2 will search for E2’s value, not the value you happen to be viewing in another cell.
- Use Find (Ctrl+F on Windows; Command+F on Mac) to check whether the key appears in the intended lookup range. Finding a visually similar value elsewhere is not enough.
If the ranges are on another sheet, inspect the sheet name as well. A reference to last month’s worksheet or a similarly named table can be syntactically valid while searching the wrong data.
Look for data-type and formatting mismatches
A common cause is that one value is a number and the other is text that looks like a number. For example, a numeric 1042 may not match the text value “1042” in an imported code column. Number formatting alone does not change the underlying value: displaying a number with leading zeroes using a custom format does not make those zeroes part of the stored number.
Check a sample value on both sides of the lookup. In Excel, a green error indicator may identify a number stored as text, but its absence does not prove that two values have the same type. If codes can contain leading zeroes, treat them consistently as text; converting “00142” to a number would lose information. For genuinely numeric IDs stored as text, standardize the source data or convert the lookup value carefully.
With E2 containing numeric text, B2:B100 containing actual numbers, and C2:C100 containing results, you can convert the lookup value to a number like this: =XLOOKUP(VALUE(E2),B2:B100,C2:C100,"No match")
This conversion is appropriate only when E2 contains a valid number and the identifiers are meant to be numeric. It is not suitable for codes with significant leading zeroes or letters. If the source column is inconsistent, cleaning the data into a consistent helper column is usually easier to audit than applying a conversion inside every lookup.
Check spaces, hidden characters, and spelling
Text can look identical while containing extra characters. A leading or trailing space, a nonbreaking space copied from a website, or an invisible control character can prevent an exact match. Punctuation, accents, and spelling differences matter too. XLOOKUP’s ordinary exact text match is generally not case-sensitive, so capitalization by itself is unlikely to explain the error.
TRIM removes ordinary extra spaces around text and reduces repeated internal spaces, but it does not remove every kind of invisible character. CLEAN removes many nonprinting characters, though it does not remove nonbreaking spaces by itself. For recurring imports, clean the lookup column consistently and use the cleaned column for matching rather than repeatedly patching individual formulas.
With the original keys in A2:A100, a cleaned helper column in D2:D100, a search value in E2, and return values in C2:C100, enter this in D2 and fill it down: =TRIM(CLEAN(A2))
Then, with E2 also cleaned or known to contain ordinary text, search the helper column: =XLOOKUP(TRIM(CLEAN(E2)),D2:D100,C2:C100,"No match")
These formulas assume each cleaned key still uniquely identifies the intended record. Cleaning can make two formerly different entries identical—for example, if the only difference was an unwanted space—so check for duplicates before relying on the result.
Confirm the match mode and search direction
The optional arguments after the not-found value control how XLOOKUP matches and searches. Its default match mode is 0, meaning exact match. Match mode -1 finds an exact match or the next smaller item; mode 1 finds an exact match or the next larger item; mode 2 enables wildcard matching. An approximate match can return a result when an exact value does not exist, but it is not a general fix for a missing ID.
Search mode is separate from match mode. The default searches from the first item to the last. Mode -1 searches from last to first, which can return the most recent occurrence when a key is duplicated. Modes 2 and -2 use binary search and require data sorted in the appropriate order; using them on unsorted data can produce invalid results, including an apparent failure to find an item that is present. For most ordinary lists, leave search mode at its default.
Wildcards can help when the intended match is partial text. With a fragment in E2, names in A2:A100, and corresponding departments in C2:C100, this searches for a name containing that fragment: =XLOOKUP("*"&E2&"*",A2:A100,C2:C100,"No match",2)
In wildcard mode, an asterisk represents any number of characters and a question mark represents one character. Wildcards are useful for text searches, not numeric approximations. If the text you are searching for includes a literal asterisk or question mark, wildcard rules need special handling; otherwise, Excel may interpret that character as a pattern rather than as part of the value.
Use the not-found argument without hiding other errors
When a missing match is an expected possibility, supply a clear message as XLOOKUP’s fourth argument. That makes the result easier for a report reader to understand and avoids wrapping the whole formula in an error-suppression function.
With E2 as the search value, A2:A100 as the lookup range, and C2:C100 as the return range, use: =XLOOKUP(E2,A2:A100,C2:C100,"Product not found")
The message appears when XLOOKUP finds no match. It does not repair bad data, and it should not be used to make an incorrect range look successful. Also distinguish a missing match from an error stored in the matched return cell: if the corresponding cell in C2:C100 itself contains #N/A, XLOOKUP can return that error even though it found the lookup value.
If you specifically want to replace any #N/A result—including one originating in the returned value—you can use IFNA. With the same E2, A2:A100, and C2:C100 layout, use: =IFNA(XLOOKUP(E2,A2:A100,C2:C100),"Check source data")
IFNA handles only #N/A; it leaves other errors visible. IFERROR is broader and can hide errors such as #VALUE! or #REF! as well. Broad suppression may make a report look tidy while concealing a broken reference or another problem, so use it only when that behavior is intentional.
Distinguish #N/A from other XLOOKUP problems
Not every failed XLOOKUP is a not-found result. #VALUE! can indicate that the lookup and return arrays have incompatible dimensions. #REF! points to an invalid reference, such as a deleted range. #NAME? can mean Excel does not recognize XLOOKUP, as in an older Excel version, or that a formula name was typed incorrectly.
There is also a display-versus-value issue: a cell may show rounded numbers or formatted dates while retaining a different underlying value. XLOOKUP compares stored values, not just the way they appear. For example, two displayed dates can differ if one is text and the other is an Excel date serial. Checking the formula bar and standardizing the underlying data can reveal this kind of mismatch.
When the formula is long, test the lookup piece by piece. Select the lookup value in the formula bar and press F9 to see its evaluated value, then press Esc to avoid replacing the formula accidentally. You can also temporarily test a simple XLOOKUP against a small, known range. On Mac, function keys may require the Fn key depending on keyboard settings; Excel for the web uses the same formula logic, though keyboard behavior can depend on the browser.
Frequently Asked Questions
Why does XLOOKUP return #N/A when the value is visibly in the list?
It may be outside the referenced range, stored as a different data type, or contain spaces or hidden characters. Confirm the exact cell references and compare the underlying values, not just their displayed appearance. Also verify that the lookup array is the column that actually contains the key.
How do I show a blank instead of #N/A?
With the lookup value in E2, keys in A2:A100, and results in C2:C100, use =XLOOKUP(E2,A2:A100,C2:C100,""). The empty text result makes an unmatched lookup appear blank. Consider whether a blank could be mistaken for a valid empty result; a clear label such as “Not found” is often easier to review.
Is XLOOKUP case-sensitive?
No. A normal XLOOKUP exact text match does not distinguish uppercase from lowercase. If case must matter, XLOOKUP alone is not enough; a case-sensitive approach needs a different formula design, typically using EXACT with an array calculation.
Does XLOOKUP return the first or last duplicate?
By default, it returns the first match it encounters, searching from the start. Set the search mode to -1 to search from the end and return the last match. This is useful only if returning one duplicate is appropriate; if duplicates indicate a data-quality issue, identify and resolve them instead.
Why does XLOOKUP show #N/A on an older Excel version?
In Excel 2019 or earlier, XLOOKUP is not available, so the formula generally produces #NAME? rather than #N/A. Use a compatible alternative such as INDEX and MATCH, or open the workbook in Microsoft 365 or Excel 2021 or later. Excel for Mac and Excel for the web support XLOOKUP when the available Excel version includes the function.
Related
- Excel: Subtract the Same Value From Multiple Cells
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
- Excel Data Validation Lists: Create and Manage Dropdowns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Not Working? Fix Numbers Stored as TextYes—VLOOKUP can return #N/A when the lookup value is a number but the matching value in the table…
- Find Tool Not Working in Excel? Fix Find and ReplaceIf Excel’s Find tool is not working, press Ctrl+F, open Options in the Find and Replace dialog, and…
- Excel Divide by Zero Error: Causes and Practical FixesTo fix a #DIV/0! error in Excel, check that the formula’s divisor is not zero or blank, then…
- Excel Hyperlink to Another Sheet Not Working? FixesIf an Excel hyperlink to another sheet is not working, check that the destination sheet exists and is…
- Excel CSV Import: Open and Fix Files in Microsoft 365To import a CSV file into Excel for Microsoft 365 on Windows, select Data > Get Data >…
- COUNTIFS Not Counting Correctly? Fix the CriteriaIf COUNTIFS is returning zero or the wrong total in Excel, first check that every criteria range has…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.