In Excel, XLOOKUP’s “value not available” message usually means it could not find the lookup value; the formula displays this as #N/A by default. Check that the value really exists in the lookup range and that its data type and characters match, or provide XLOOKUP’s optional if_not_found argument to show a clearer message.
What the XLOOKUP error means
XLOOKUP searches one range for a value and returns a corresponding value from another range. If it cannot find a match, its default result is #N/A. Excel may describe that error as “A value is not available to the formula or function.” The wording can make it sound as if a feature or cell is missing, but in an XLOOKUP formula it most often means that the search did not produce a match.
For example, a formula might look for an employee ID in a list, but the ID is absent, contains an extra space, or is stored differently from the value being searched for. Those cases can look identical on the worksheet: the formula returns #N/A, even though the reason differs. Finding the cause matters, especially when a missing match should be treated as a data problem rather than quietly hidden.
The optional fourth XLOOKUP argument lets you replace the default not-found result with text, a number, or another value. This changes what appears when there is no match; it does not repair the underlying data or fix every possible formula error.
How XLOOKUP searches
The function’s basic structure is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The lookup value is what Excel searches for, the lookup array is where it searches, and the return array contains the corresponding results. The lookup and return arrays need to align: if the search range has one row for each record, the results range should have the same number of rows.
By default, XLOOKUP uses exact matching and searches from the first item to the last. It does not require the data to be sorted. If you omit if_not_found, a failed search returns #N/A. The match-mode options can change the kind of match: 0 means exact (the default), -1 means exact or next smaller, 1 means exact or next larger, and 2 enables wildcard matching. Approximate and binary searches need careful setup; an incorrectly sorted range can produce an incorrect result rather than a clear warning.
For ordinary lists such as product codes, names, or invoice numbers, leave the match and search modes at their defaults unless you have a specific reason to change them. This avoids turning a simple missing exact match into a surprising approximate or wildcard result.
Check the formula and the lookup range
Start with the formula itself. Confirm that the lookup value points to the intended cell, that the lookup array contains the column or row where that value lives, and that the return array contains the requested results. A formula can be syntactically valid but search the wrong column, use a shifted range, or return values from records that do not correspond to the search range.
- Click the cell showing
#N/Aand inspect the formula bar. Identify the lookup-value reference and the two ranges. - Use the Name Box or select the referenced ranges to verify that they cover the expected records and have matching dimensions.
- Search the lookup range for the value you expect to find. If the value is absent, check whether the source data is incomplete or whether the formula points to the wrong list.
- Compare the actual lookup value with the candidate value in the list, including spaces, punctuation, and whether one is text while the other is numeric.
- Check whether the source data may have changed: a new row might be outside a fixed range, or a formula might refer to an old sheet or workbook.
When you inspect the formula, remember that XLOOKUP can search vertically or horizontally. The lookup and return ranges do not have to be adjacent, but they must correspond in size and direction. For example, a vertical lookup range should be paired with a vertical return range of the same height. A mismatch in range dimensions is more likely to cause a different error, such as #VALUE!, but it is still worth checking while troubleshooting.
Common reasons an existing value is not found
- Extra spaces or hidden characters: A code may contain a leading or trailing space, a nonbreaking space copied from a webpage, or another invisible character. The cells can look alike while comparing differently.
- Text versus number: The number
123and the text value"123"are not always treated as the same lookup value. Number formats can make two different underlying values appear identical. - Punctuation or spelling differences: Hyphens, apostrophes, leading zeros, and small spelling differences can prevent an exact match. A code such as
0074may also lose its leading zeros if it was imported as a number. - Wrong range or omitted records: A fixed range may not include newly added rows. A reference may also point to a similar-looking column on another worksheet.
- Unexpected match mode: If the fifth argument is
1,-1, or2, XLOOKUP is not using its ordinary exact-match behavior. Review all arguments, not just the first three. - Different characters that look similar: Letters from different alphabets or visually similar symbols can be distinct. This can happen with pasted data from external systems.
Excel’s ordinary exact text matching is not case-sensitive, so a difference such as abc versus ABC alone normally does not explain a missing XLOOKUP match. Do not spend time changing letter case unless the formula uses a separate case-sensitive method.
Return a useful result when no match exists
If a missing value is an expected possibility, use the fourth argument to replace the default error with a message that tells the spreadsheet user what happened. With product IDs in A2:A20, product names in B2:B20, and the ID to search for in D2, use:
=XLOOKUP(D2,A2:A20,B2:B20,"Product ID not found")
If D2 contains an ID in the lookup list, the formula returns its product name; if there is no match, it displays Product ID not found instead of #N/A. The message is useful for reports and forms, but it can also conceal a typo or a missing record. For a quality-control sheet, a clear warning is often better than a blank result because it prompts someone to investigate.
You can return a blank when no match is found, but that makes an unsuccessful lookup harder to distinguish from a genuinely empty result. If you choose that presentation, consider adding a separate validation check or using a visible message during data entry. The fourth argument handles the not-found condition; it does not suppress unrelated problems such as a broken reference or an error already present in the data.
Fix spaces and data-type mismatches
For ordinary extra spaces, a helper column can clean the source values. With the unclean text in A2, enter this in a separate cell or column and fill it down:
=TRIM(A2)
TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to single spaces. Clean the lookup value and the lookup-list values consistently; cleaning only one side can leave the mismatch in place. Keep the original column until you have checked the results, since cleaning can change data that was intentionally formatted in a particular way.
TRIM does not remove every invisible character. In particular, text copied from web pages or other systems may include nonbreaking spaces. If ordinary trimming does not work, inspect the source, try replacing the problematic character in a helper column, or re-import the data using a clean source. Avoid broad replacements without checking first: punctuation and spaces can be meaningful parts of an identifier.
For numbers stored as text, first decide which representation is correct for the data. Converting an ID to a number can remove leading zeros, while converting a number to text may require a consistent format. For identifiers, preserving the original text is often important; for quantities, consistent numeric values are usually more appropriate. Apply the correction to both the search value and the lookup list, rather than relying on how cells look on screen.
Wildcard matches and other error handling
When the search should find a partial text match, explicitly choose wildcard mode. For example, with descriptions in A2:A20, corresponding categories in B2:B20, and a search pattern in D2 that contains *North*, use:
=XLOOKUP(D2,A2:A20,B2:B20,"No matching description",2)
The asterisk wildcard represents any number of characters, and the question mark represents one character. A tilde can be used to treat a wildcard character as a literal character. Wildcard mode is not the default, so adding 2 as the fifth argument is essential. Partial matching can return the first matching entry, which may not be unique; make sure that is appropriate for the data.
If you still see #N/A after adding a not-found message, the error may be coming from a different part of the formula or from a value in the return range. XLOOKUP’s fourth argument is for a lookup that finds no match. A wrapper such as IFNA can replace a resulting #N/A more broadly, but it can also hide a real issue. Use it only when treating every #N/A from that expression the same way is intentional.
Excel version and compatibility
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is not available as a built-in function in Excel 2019 or Excel 2016. In an older version, a workbook containing XLOOKUP may show a name-related error or an unsupported-function indicator, depending on how the file is opened; adding an if_not_found argument will not make that version support XLOOKUP.
If a workbook must work in an older Excel release, alternatives include INDEX with MATCH or VLOOKUP, chosen to fit the layout and compatibility needs. Those functions have different behavior and argument requirements, so replacing XLOOKUP is not always a matter of changing the function name. Microsoft 365 for Windows uses the same basic XLOOKUP syntax described here as Excel for Mac and Excel for the web, although ribbon locations and some interface details can differ. The formulas themselves do not require a special Windows-only setting.
Frequently Asked Questions
Why does XLOOKUP return #N/A when the value is visibly in the list?
Check for spaces or hidden characters, text-versus-number differences, a misplaced range, and spelling or punctuation differences. The default match is exact, so values that look alike may not be identical to Excel.
How do I replace #N/A with “Not found” in XLOOKUP?
Add "Not found" as the fourth argument, after the return array. That argument changes the result when XLOOKUP cannot find a match; it does not correct a bad reference or every other formula error.
Is XLOOKUP case-sensitive?
No. Its standard text matching is not case-sensitive, so uppercase and lowercase differences alone generally do not cause a not-found result.
Can XLOOKUP find part of a text value?
Yes. Set the fifth argument to 2 for wildcard matching and include an asterisk or question mark in the lookup pattern. Remember that the first matching result is returned, so partial matches can be ambiguous.
Why does XLOOKUP work on one computer but not another?
The computer with the error may be using Excel 2019 or an earlier release, which does not include XLOOKUP. Use Microsoft 365 or Excel 2021 and later, or replace the formula with a compatible lookup method if the workbook must support older versions.
Does the fourth XLOOKUP argument fix every #N/A error?
No. It supplies a result when the lookup value is not found. If the return value itself contains #N/A, or another part of the formula produces an error, the fourth argument may not remove that error. Trace the source before masking it.
Related
- Excel Shortcut to Refresh a PivotTable and All Data
- Excel Shortcut to Select All Data in a Column Quickly
- Excel Shortcut to Select a Whole Column in Seconds
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel FILTER #VALUE! Error: Causes and FixesIn Excel, a FILTER formula usually returns #VALUE! when its include array is not the same height or…
- Excel SUMIFS #VALUE! Error: Causes and FixesIn Excel, SUMIFS most commonly returns #VALUE! when its sum range and one or more criteria ranges have…
- Excel MAX Not Working? Common Causes, Checks, and FixesIf Excel MAX is not giving the result you expect, first check that the cells contain real numbers…
- Excel Not Recognizing Numbers? Fix Text-Formatted CellsExcel usually fails to recognize numbers because the cells contain text that looks numeric, often after a paste…
- Excel PivotTable Average #DIV/0! Error: Causes and FixesAn Excel PivotTable showing #DIV/0! for an Average usually has an error in its source data or in…
- VLOOKUP Not Working? Fix Common Excel Lookup ErrorsIf VLOOKUP is not working, first check that the lookup value is in the leftmost column of the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.