Formula Errors

How to Fix VLOOKUP Returning 1900 Date for Blank Cells

Steve K Oct 7, 2026 871 views

Question details

The user needs to prevent VLOOKUP from displaying a 1900 date (like 01/00/1900 00:00) when the referenced source cell is blank.

How to Fix VLOOKUP Returning 1900 Date Instead of Text

Product: Spreadsheet
Device & OS: not provided
Scenario: Using a VLOOKUP formula to retrieve date values where some of the referenced source cells might be empty.
Observed behavior: VLOOKUP returns a zero for blank source cells, which the spreadsheet automatically formats as the date 01/00/1900 00:00, instead of displaying expected text such as "No Appt Date".

Before you start

Verify that the source cells are genuinely blank and do not contain hidden spaces or formatting that might interfere with formula evaluations.

Solution 1Recommended

Use an IF Statement to Check for Zero Values

Wrap your VLOOKUP formula in an IF function to display specific text when a blank cell evaluates to zero.

When VLOOKUP references a completely blank cell, it interprets the empty value as a zero. If the destination cell is formatted as a date, the spreadsheet converts that zero into the default starting date of its calendar system: January 0, 1900.

Select the target cell

Click on the cell where you want to enter your VLOOKUP formula and display the result.

Enter the combined IF and VLOOKUP formula

Type the formula: =IF(VLOOKUP(E2,A:C,2,FALSE)=0,"No appt date",VLOOKUP(E2,A:C,2,FALSE)). Adjust the cell references (E2, A:C, 2) to match your actual dataset.

Apply the formula

Press Enter to apply the formula. Now, if the source cell is blank, the formula detects the zero and displays 'No appt date' instead of '01/00/1900'.

Use an IF Statement to Check for Zero Values

Displaying a Blank Cell: You can replace "No appt date" with "" (two double quotes without a space) in the formula if you prefer the cell to appear completely blank when the source is empty.

Solution 2

Use XLOOKUP with a Zero Check

If you are using a modern spreadsheet version, you can substitute VLOOKUP with XLOOKUP to achieve the same result with potentially better performance.

Manage Complex Formulas Easily

Fix Spreadsheet Errors Effortlessly with WPS Office

WPS Spreadsheet provides robust formula support, including VLOOKUP, XLOOKUP, and IF functions. It handles complex data formatting and nested formulas seamlessly, making it easy to clean up your datasets.

1. Open your document: Launch WPS Spreadsheet and open the file containing your VLOOKUP formulas.
2. Select the formula cell: Click on the cell displaying the incorrect 1900 date.
3. Update the formula: Modify the formula bar to include the IF statement, such as =IF(VLOOKUP(...)=0, "", VLOOKUP(...)).
4. Apply to the column: Press Enter, then drag the fill handle down to apply the corrected formula to the rest of your data.

Fully compatible with Microsoft Excel formulas, date formatting, and file types.Advanced functions like XLOOKUP and nested IF statements are fully supported.Lightweight software that calculates large datasets quickly without lagging.Free to use with an intuitive, user-friendly interface for immediate productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why do spreadsheets turn blank cells into 1900 dates?

When a formula like VLOOKUP references an empty cell, it retrieves a numerical 0 instead of a blank value. If the destination cell has a Date format applied, the system translates the number 0 into the very first day of its date system, which is typically January 0, 1900.

Can I just hide zero values in the entire worksheet to fix this?

Yes, you can hide zero values globally by going to Options > View and unchecking the 'Zero values' option. However, this will hide legitimate mathematical zero values everywhere in your sheet, so using an IF formula is usually a safer and more targeted approach.

Does the newer XLOOKUP function have the same issue with blank cells?

Yes, XLOOKUP will also return a 0 if the matched return cell is empty. While XLOOKUP has an 'if_not_found' argument for missing matches, it does not apply to found matches with blank return cells. You still need to use an IF statement (e.g., =IF(XLOOKUP()=0, "", XLOOKUP())) to prevent the 1900 date from appearing.

Excel & Data Problems