Formula Errors

How to Return Blank Cells Instead of Zeros in XLOOKUP

Huma Ashraf Ch Sep 28, 2026 869 views

Question details

The user needs to make the XLOOKUP function return a blank cell instead of a 0 (or a zero-based date like 00/01/1900) when the matched source cell is empty.

How to Return Blank Cells Instead of Zeros in XLOOKUP

Product: Spreadsheet
Device & OS: not provided
Scenario: Extracting or referencing data using the XLOOKUP function where some of the matched source cells do not contain any data.
Observed behavior: When XLOOKUP points to an empty cell, it defaults to returning a 0. If the destination cell is formatted as a date, this 0 translates to the date 00/01/1900.

Before you start

Ensure your destination cells are formatted correctly for your expected data type, and verify that the source cells are truly blank (not containing hidden spaces).

Solution 1Recommended

Wrap XLOOKUP with an IF Function

Use an IF function to evaluate the XLOOKUP result first. If the result is zero, output a blank string; otherwise, output the actual XLOOKUP result.

By default, spreadsheet software interprets empty cells as zero in lookup formulas. Using an IF wrapper forces the software to explicitly return a blank string instead of a numerical zero.

If you are automating your reports with macros, it is highly recommended to test this formula in the destination sheet first before applying it through VBA or Office Scripts.

Select the destination cell

Click on the cell where you want your lookup result to appear.

Enter the IF and XLOOKUP combined formula

Type the formula: =IF(XLOOKUP(Sheet2!A2,Sheet1!A:A,Sheet1!C:C)=0,"",XLOOKUP(Sheet2!A2,Sheet1!A:A,Sheet1!C:C)) substituting the cell references with your actual data ranges.

Apply the formula

Press Enter to evaluate the formula, then click and drag the fill handle down to apply it to the rest of your column.

Formula applied correctly: Your XLOOKUP formula will now seamlessly return empty cells instead of disrupting your data with unwanted zeros or 1900 dates.

Solution 2

Append an Empty String to XLOOKUP (For Text Data Only)

If you are only looking up text values, a quick shortcut is to append an empty string (&"") to the end of your formula.

Advanced Spreadsheet Tool

Effortlessly Manage Data and Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP, IF wrappers, and complex nested formulas, giving you the power to clean and organize your datasets seamlessly.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the lookup data.
2. Enter the IF wrapper formula: Select the target cell and type =IF(XLOOKUP(...)=0,"",XLOOKUP(...)) utilizing WPS's auto-complete feature.
3. Review the clean results: Press Enter to see blank outputs instead of zeros, keeping your data tables clean.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Built-in error checking to troubleshoot formula issues quicklyLightweight software with lightning-fast performance for large datasetsFree to use with an intuitive, familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP return 00/01/1900 for blank cells?

Spreadsheet applications treat blank cells as 0 in mathematical and lookup functions. When a 0 is returned to a cell that is formatted as a Date, it translates to the system's default zero date, which is January 1, 1900.

Can I use the [if_not_found] argument in XLOOKUP to handle blanks?

No. The [if_not_found] argument only triggers when the lookup value itself is missing from your lookup array. It does not control what happens when a match is found but the corresponding return cell is empty.

Will running XLOOKUP twice in an IF statement slow down my spreadsheet?

For most everyday spreadsheets, modern computers process this instantly. However, if you are calculating hundreds of thousands of rows, you can optimize performance using the LET function: =LET(x, XLOOKUP(...), IF(x=0, "", x)).

Excel & Data Problems