Solved VLOOKUP Returns 00/01/1900 Instead Of Blank

June 20, 2018 at 21:31:25
Specs: Windows 7
Windows 10 pro.

Hi, i have noticed there have been afew problems with this vlookup formula returning 00/01/1900 on linked sheets when hoping to return a blank cell.
i have tried numerous formulas and it either returns TRUE, FULSE or #NAME?.....
the formula i am using is as follows
=VLOOKUP(A5,'Generics - Skills'!A:O,15,FALSE)
Could anyone help me with an IF formula that will return a blank?

Many Thanks

message edited by Ashhhhh


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

Reply ↓  Report •

✔ Best Answer
June 21, 2018 at 07:45:12
Try this:

=IF(VLOOKUP(A5,'Generics - Skills'!A:O,15,FALSE)="","",VLOOKUP(A5,'Generics - Skills'!A:O,15,FALSE))

MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
June 21, 2018 at 01:35:25
I'm sorry but you mean in excel? You're asking this in "windows7/configurations"
In which case right click the cell --> format cells... --> select number --> press OK

I think exel tried to autodetect the Cell format & thought it was a date.
Apparently value 0 = 0-1-1900

I can confirm, just tested it in excel.
To re-create find a random empty cell, give it value 0, then format cell as date

i5-6600K[delid]@4.8GHz/4.4GHz@1.39v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-13-13-28 1T 2800MHz@1.37v
MSI Armor RX 570 4GB@1430Mhz core@1.256v/1920MHz BiosModded


Reply ↓  Report •

#2
June 21, 2018 at 01:39:36
Yeah I ment excel.

I tried to delete and repost under correct category but wouldn't let me.

It's pretty much carrying a date in a sheet under a vlookup name to another sheet under same heading.

Pre planning and setting up the formula prior to entering a date returns the defalt date I'm guessing.

message edited by Ashhhhh


Reply ↓  Report •

#3
June 21, 2018 at 02:10:45
yep, you could add some conditional formatting, like making the entire cell white+white text if value = '0'

i5-6600K[delid]@4.8GHz/4.4GHz@1.39v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-13-13-28 1T 2800MHz@1.37v
MSI Armor RX 570 4GB@1430Mhz core@1.256v/1920MHz BiosModded


Reply ↓  Report •

Related Solutions

#4
June 21, 2018 at 02:52:00
Yessss that will work.
Great thinking thanks.

Reply ↓  Report •

#5
June 21, 2018 at 07:45:12
✔ Best Answer
Try this:

=IF(VLOOKUP(A5,'Generics - Skills'!A:O,15,FALSE)="","",VLOOKUP(A5,'Generics - Skills'!A:O,15,FALSE))

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#6
June 21, 2018 at 20:34:23
Mike you are a legend i tried this all it needed was an =
Thanks mate I've only been using excel for 1 month this has helped me so much.
Cheers.

Reply ↓  Report •

Ask Question