How to Replace Excel #N/A Errors with Blanks Using XLOOKUP
Question details
The user wants to return a blank cell instead of an #N/A error when a lookup formula fails to find a match or when the lookup reference cell is empty.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Pulling data like employee pay or job names from a source table where some missing or unmatched entries cause unsightly #N/A errors on the worksheet.
- Observed behavior
- Standard lookup formulas display an #N/A error when a value is not found. The goal is to suppress this error and display a clean, blank cell instead.
Ensure that your spreadsheet software supports the modern XLOOKUP function. If you are using an older version of Excel, you may need to use IFERROR with VLOOKUP or INDEX/MATCH instead.
Use XLOOKUP's Built-in 'If Not Found' Argument
XLOOKUP natively includes an argument specifically designed to handle missing values, eliminating the need for complex nested error-handling functions.
The XLOOKUP function has a fourth parameter called [if_not_found]. By defining this parameter as an empty string, the formula will automatically return a blank cell instead of an error when no match exists.
Click on the cell where you want the lookup result to appear.
Type the standard XLOOKUP formula to find your data. For example: =XLOOKUP(C5, JOB_NAME, JOB_DATA).
Add a comma to access the fourth argument, and enter double quotation marks "" to represent a blank. The formula should look like: =XLOOKUP(C5, JOB_NAME, JOB_DATA, "").
Press Enter. The cell will now remain cleanly blank if C5 is not found in the JOB_NAME range.

Handle Lookups for Cells Containing Empty Strings
Use an IF condition combined with XLOOKUP when your lookup reference cell contains a formula that returns an empty string, rather than being truly empty.
Effortlessly Handle Data Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced modern formulas like XLOOKUP, allowing you to streamline your workflow and handle missing data gracefully without complex nested formulas.
- 1. Open your workbook: Launch WPS Spreadsheet and open your data file containing the lookup tables.
- 2. Trigger the formula: Click the destination cell and type =XLOOKUP( to bring up the formula syntax hint.
- 3. Set parameters: Fill in the lookup value, lookup array, and return array, then enter "" as the fourth argument to seamlessly replace any errors with blanks.

Frequently Asked Questions
Why does XLOOKUP return #N/A instead of a blank automatically?
XLOOKUP returns an #N/A error by default when a match isn't found if the fourth argument ([if_not_found]) is omitted. You must explicitly define this argument as "" to output a blank.
Can I use VLOOKUP to return a blank instead of #N/A?
Yes, but because VLOOKUP lacks a built-in 'if not found' parameter, you have to wrap the entire VLOOKUP formula inside an IFERROR function, like this: =IFERROR(VLOOKUP(A1, B:C, 2, FALSE), "").
How do I return a zero (0) instead of a blank when there is no match?
Simply replace the empty quotes "" in the fourth argument of your XLOOKUP formula with a zero (0). The formula would look like: =XLOOKUP(C5, JOB_NAME, JOB_DATA, 0).
Can I use CHOOSECOLS within my XLOOKUP formula?
Yes, XLOOKUP integrates perfectly with dynamic array functions. You can use CHOOSECOLS for the return array parameter, e.g., =XLOOKUP(C5, JOB_NAME, CHOOSECOLS(JOB_DATA, 4), ""), which fetches data specifically from the 4th column of the JOB_DATA array.




