How to deal with the VLOOKUP function not displaying dates correctly.(Microsoft Excel)
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.
Steps
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.
=IFERROR(VLOOKUP(B3,E3:F6,2,FALSE),"")
In the case of the XLOOKUP function, the fourth argument is used.
=XLOOKUP(B3,E3:E6,F3:F6,"")
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.
=IFERROR(IF(VLOOKUP(B3,E3:F6,2,FALSE)=0,"",VLOOKUP(B3,E3:F6,2,FALSE)),"")=IF(XLOOKUP(B3,E3:E6,F3:F6,"")=0,"",XLOOKUP(B3,E3:E6,F3:F6,""))
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.
=VLOOKUP(B3,E3:F6,2,FALSE)&""
In this case, stop concatenating whitespace strings and use the aforementioned formula.
---