How to Fix XLOOKUP Returning #N/A for Non-Matching Rows in Excel
Question details
The user wants to prevent the XLOOKUP function from displaying #N/A errors when a lookup value is not found, aiming to show only matched rows or blank cells instead.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using the XLOOKUP function to retrieve data from another range or sheet where some lookup values do not exist in the source array.
- Observed behavior
- The formula returns an #N/A error instead of leaving the cell blank or filtering out the unmatched records entirely.
Ensure that your source data does not contain hidden leading or trailing spaces, as formatting differences can cause valid matches to fail and return an #N/A error.
Utilize the Built-in 'If_not_found' Argument in XLOOKUP
The most direct way to handle missing matches is to use XLOOKUP's optional fourth argument to return a custom value, such as a blank cell.
Unlike VLOOKUP, which requires wrapping the formula in IFERROR, XLOOKUP has error handling natively built-in. By defining the [if_not_found] argument, you tell the software exactly what to display when a match cannot be located.
Select the cell containing your current XLOOKUP formula.
Modify the formula by adding a comma after the return_array to access the [if_not_found] argument.
Type two double quotes ("") as the fourth argument. For example: =XLOOKUP($AG$3, 'Current Year Projects'!F:F, 'Current Year Projects'!B:B, "").
Press Enter and drag the fill handle to apply the updated formula to the rest of your column. Unmatched rows will now display as blank.

Use the FILTER Function to Display Only Matching Records
Use this solution if your goal is to extract a list of only the rows that match a specific criteria, completely omitting blank or #N/A rows.
Clean Source Data to Prevent False Mismatches
Sometimes #N/A appears because the lookup value and source data look identical but have hidden spaces or different data types.
Use WPS Spreadsheet to Handle Complex Lookups Efficiently
WPS Spreadsheet fully supports advanced functions like XLOOKUP and FILTER, allowing you to seamlessly manage data extraction and error handling. It offers a lightweight, user-friendly interface to build dynamic reports.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing workbook containing the lookup data.
- 2. Select target cell: Click on the cell where you want to output the lookup result.
- 3. Insert formula: Type =XLOOKUP(lookup_value, lookup_array, return_array, "") to fetch data while hiding #N/A errors.
- 4. Review data: Press Enter to instantly process the data with perfect formula compatibility.

Frequently Asked Questions
Why does my XLOOKUP return #N/A even when the exact value exists?
This usually happens due to formatting inconsistencies, such as leading or trailing spaces, or when numbers are stored as text in one column but as numeric values in the other. Use the TRIM or VALUE functions to clean your data.
Can I return a custom text message instead of a blank cell with XLOOKUP?
Yes. In the fourth argument of the XLOOKUP function, you can type any text enclosed in double quotes, such as =XLOOKUP(A2, B:B, C:C, "Missing Data").
What is the difference between IFERROR and the XLOOKUP not-found argument?
The XLOOKUP 'if_not_found' argument only handles instances where the lookup value is missing. IFERROR will mask any formula error, including syntax errors or division by zero (#DIV/0!), which might hide deeper issues in your spreadsheet logic.
Is XLOOKUP available in all versions of spreadsheet software?
XLOOKUP is available in modern spreadsheet applications like WPS Office and Microsoft 365. Older versions like Excel 2016 or 2019 do not support it natively; in those versions, you would need to use an IFERROR(VLOOKUP(...)) combination instead.




