# Solved VLOOKUP Returns 00/01/1900 Instead Of Blank

January 24, 2018 at 20:15:18
Specs: Windows 7
 Hi all,I have a formula which was originally showing #N/A as well as 0/01/1900.I am using Excel 2016I was able to get rid of the #N/A but I cannot get rid of the 0/01/1900 any suggestions would be awesomeThe formula I am using is as follows=IFERROR(VLOOKUP(\$A2,'CoP Expiry'!\$A\$2:\$B\$6000,2,FALSE),"")

See More: VLOOKUP Returns 00/01/1900 Instead Of Blank

#1
January 25, 2018 at 10:19:35
 Since I know nothing about your data, I have to make a few assumptions here:1 - The cell with the VLOOKUP is formatted as a date2 - Your date format is DD/MM/YYYY3 - The value being returned by VLOOKUP is sometimes 0 (zero)If those assumptions are correct the issue is that entering/returning a 0 in a cell that is formatted as a date returns the very first day of the Excel calendar, which is January 0, 1900.I can think of 2 options:1 - Eliminate the zeros from the data set on the 'CoP Expiry' page so that a 0 is never returned.2 - Stop Excel from turning the 0 into a date by forcing a "" (empty string) if VLOOKUP returns #N/A or 0:=IF(OR(ISNA(VLOOKUP(\$A2,'CoP Expiry'!\$A\$2:\$B\$6000,2,0)),VLOOKUP(\$A2,'CoP Expiry'!\$A\$2:\$B\$6000,2,0)=0),"",VLOOKUP(\$A2,'CoP Expiry'!\$A\$2:\$B\$6000,2,0))Now, if you actually need a 0 to be displayed in the cell then let us know and we'll work on that.

Report •

#2
January 29, 2018 at 23:14:48
 Hi there DerbyDad03,Thanks for your advice.Further info is that the VLookup is a pay number and it is searching another worksheet for expiry datesI have tried your formula and it has gotten rid of the 1/1/1900 issue but the #N/A is still there. Any ideas?

Report •

#3
January 30, 2018 at 06:53:32
✔ Best Answer
 Try this:=IF(ISNA(VLOOKUP(\$A2,'CoP Expiry'!\$A\$2:\$B\$6000,2,0)),"",IF(VLOOKUP(\$A2,'CoP Expiry'!\$A\$2:\$B\$6000,2,0)=0,"",VLOOKUP(\$A2,'CoP Expiry'!\$A\$2:\$B\$6000,2,0)))Excel didn't like it if the VLOOKUP returned #N/A within the OR. The #N/A error took precedence over the OR.Just a tip:If you had used the Formula Evaluator feature you would have seen that.message edited by DerbyDad03

Report •

Related Solutions

#4
January 30, 2018 at 13:42:11
 DerbyDad03You are a champion, that fixed it. Thanks a million.

Report •

#5
January 30, 2018 at 14:20:31
 So, I am 99.9% there as I have found one other issue which is similar to my previousI have duplicated your solution across three columns and it works perfectly (columns G, H & I)But in Column J, I am looking to fill the cell with the most recent date columns G, H & IEG G2 = 27/07/2019, H2 = Blank & I2 = 30/10/2020 then I would want J2 to equal 30/10/2020 as it is the latest date in the range however, I am still getting 0/01/1900 in the J column where all of the G,H & I cells are emptyThe formula that I am using is =IFERROR(MAX(Table2[@[CoP Expiry]:[ACV expiry (Comms Only)]]),"")

Report •

#6
January 30, 2018 at 16:30:23
 I'm not sure why you are using IFERROR. What error are you trying to hide/avoid?If I start with this (using my date format)``` G H I J 2 7/27/2019 10/30/2020 =MAX(G2:I2) ```I get this: 10/30/2020Isn't that what you want?message edited by DerbyDad03

Report •

#7
January 30, 2018 at 16:49:48
 Sorry,What I am getting is 0/01/1900 when G, H & I are ALL blank. This happens when the individual hasn't completed ANY training at all for those fields.I am not getting the error where there is at least one date entered for the columns but I am when ALL of the cells are blank.

Report •

#8
January 30, 2018 at 17:08:08
 Think about the logic of why Excel returns 0/1/1900 when all cells are blank. As noted earlier, if a cell formatted as a Date contains a zero, Excel will returns the start of time as it knows it: January 0, 1900What is the MAX value in 3 blank cells? What is the MIN value? What is the SUM? The answer is Zero.Zero isn't an error, it's a valid value, so IFERROR isn't going to help.Just write a simple IF function to return "" when MAX returns zero and the MAX date when it doesn't.message edited by DerbyDad03

Report •