How to deal with the VLOOKUP function not displaying dates correctly.(Microsoft Excel)

Japanese version.

When displaying a date as a result of the VLOOKUP function, a number may be displayed instead of a date.

Here is how to deal with it.

The same method can be used with the XLOOKUP function.


Cases where numbers are displayed.

This event is not a malfunction, but rather the serial values are displayed as they are.

In many cases, the date data is successfully retrieved, but it just looks like a serial value.

If you are not changing the result of the function to a string or concatenating strings, you need to change the display format.

First, right-click and select "Format Cells".

Change "Category" to "Date" since it is set to other than date.

Cases where #N/A error are displayed.

If no test value is found, a #N/A error occurs.

For the VLOOKUP function, use the IFERROR function.


In the case of the XLOOKUP function, the fourth argument is used.


Cases where 1900-01-00.

If the hit result is blank, it will be "1900-01-00".

This is because the search results are treated as zeros and displayed as the date January 0, 1900, which is the serial value of zero.

In this case, a formula using the IF function is used.


The LET function can be used to eliminate duplicate formulas.

=LET(vl,VLOOKUP(B3,E3:F6,2,FALSE), IFERROR(IF(vl=0,"",vl),""))
=LET(xl,XLOOKUP(B3,E3:E6,F3:F6,""), IF(xl=0,"",xl))

Cases where whitespace characters are combined.

One way to deal with a zero search result is to concatenate a blank string.


In this case, stop concatenating whitespace strings and use the aforementioned formula.



Tips. XLOOKUP & VLOOKUP & HLOOKUP(Microsoft Excel)