Function Problems

How to Stop XLOOKUP from Returning Zero or January 1900 in Excel

Huma Ashraf Ch Oct 23, 2026 868 views

Question details

The user needs to prevent the XLOOKUP function from outputting 0 or a 'January 1900' date format when the matched source cell is empty.

How to Stop XLOOKUP from Returning Zero or January 1900

Product: Excel
Device & OS: not provided
Scenario: Using the XLOOKUP function to retrieve data where some of the matched source cells in the return array might be completely blank.
Observed behavior: The formula returns a '0' for general formats or 'January 0, 1900' for date-formatted cells instead of remaining blank.

Before you start

Ensure you have identified the exact XLOOKUP formula causing the issue and verify whether the source data contains legitimately empty cells.

Solution 1Recommended

Wrap XLOOKUP in an IF Function to Return a Blank

Use an IF test to check if the XLOOKUP result is empty, and force it to display a blank string instead of a zero.

By default, Excel translates empty cells retrieved by lookup functions as zero. By wrapping your XLOOKUP formula inside an IF function, you can instruct Excel to check if the result is empty. If it is, the formula will output an empty text string, keeping your cell clean.

Select the target cell

Click on the cell in your spreadsheet that contains the problematic XLOOKUP formula.

Modify the formula

In the formula bar, modify your formula to use the IF function structure. It should look like this: =IF(XLOOKUP(...)="","",XLOOKUP(...)). For example: =IF(XLOOKUP([@[GRACE ID]],'query (54)'!A:A,'query (54)'!T:T,"")="","",XLOOKUP([@[GRACE ID]],'query (54)'!A:A,'query (54)'!T:T,""))

Apply the updated formula

Press Enter to apply the formula. Now, if the corresponding return cell is empty, the result will appear completely blank instead of displaying a 0 or a 1900 date.

Wrap XLOOKUP in an IF Function to Return a Blank

Formula Customization: Simply replace the 'XLOOKUP(...)' placeholder in the example with your specific lookup value, lookup array, and return array.

Solution 2

Append an Empty String to the XLOOKUP Formula

A quick workaround for text-based results is appending an ampersand and empty quotes to the end of your formula.

Smart Spreadsheet Solutions

Fix XLOOKUP Errors Easily with WPS Spreadsheet

WPS Office provides a powerful, free Spreadsheet tool that fully supports advanced functions like XLOOKUP and IF. You can seamlessly manage your data, handle empty cells gracefully, and ensure your reports look perfect without any 1900 date glitches.

1. Download and Install: Download and install WPS Office on your computer from the official website.
2. Open your Spreadsheet: Open your existing Excel workbook (.xlsx) using WPS Spreadsheet.
3. Edit the Formula: Locate your XLOOKUP formula and wrap it in the IF condition directly in the formula bar, just as you would in Excel.
4. Save your File: Press Enter to fix the blank cell issue, then save your perfectly formatted file.

Fully compatible with Microsoft Excel formulas and formats (.xlsx)Supports advanced lookup functions including XLOOKUP, VLOOKUP, and HLOOKUPFree, lightweight, and features an intuitive tabbed interfaceAdvanced formatting tools to keep your data clean and presentable

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel return January 1900 for blank cells?

Excel stores dates as serial numbers starting from January 1, 1900. When a formula retrieves a blank cell, it defaults to returning a 0. If that destination cell is formatted as a date, Excel interprets that 0 as the very beginning of its date system, displaying it as 'January 0, 1900'.

Can I use the 'if_not_found' argument in XLOOKUP to fix this?

No, the 'if_not_found' argument only triggers when the lookup value itself is missing from the lookup array. It does not activate when the lookup successfully finds a match but the corresponding cell in the return array happens to be empty.

Does the IF statement method work for VLOOKUP as well?

Yes, you can use the exact same logic for VLOOKUP. By structuring the formula as =IF(VLOOKUP(...)="","",VLOOKUP(...)), you can prevent 0s and 1900 dates from appearing when VLOOKUP returns an empty cell.

Excel & Data Problems