Formula Errors

How to Fix XLOOKUP Returning #N/A for Non-Matching Rows in Excel

Steve KSteve K Oct 14, 2026 872 views

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.

How to Fix XLOOKUP Returning #N/A for Non-Matching Rows in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Locate the formula

Select the cell containing your current XLOOKUP formula.

2
Edit the formula

Modify the formula by adding a comma after the return_array to access the [if_not_found] argument.

3
Add the blank string

Type two double quotes ("") as the fourth argument. For example: =XLOOKUP($AG$3, 'Current Year Projects'!F:F, 'Current Year Projects'!B:B, "").

4
Apply the formula

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.

Utilize the Built-in 'If_not_found' Argument in XLOOKUP
Custom Messages: Instead of a blank cell, you can also return a specific message like "Not Found" by typing it inside the quotation marks.

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. 1. Open WPS Spreadsheet: Launch WPS Office and open your existing workbook containing the lookup data.
  2. 2. Select target cell: Click on the cell where you want to output the lookup result.
  3. 3. Insert formula: Type =XLOOKUP(lookup_value, lookup_array, return_array, "") to fetch data while hiding #N/A errors.
  4. 4. Review data: Press Enter to instantly process the data with perfect formula compatibility.
Full compatibility with Microsoft Excel's advanced functions including XLOOKUP and FILTER.Built-in error checking and formula auditing tools for large datasets.Lightweight software that handles complex arrays without lagging.Free and highly accessible across Windows, Mac, iOS, and Android.
microsoft office alternative - wps office

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.