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

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))MIKEmessage 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 OKI think exel tried to autodetect the Cell format & thought it was a date.Apparently value 0 = 0-1-1900I can confirm, just tested it in excel.To re-create find a random empty cell, give it value 0, then format cell as datei5-6600K[delid]@4.8GHz/4.4GHz@1.39v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-13-13-28 1T 2800MHz@1.37vMSI 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.37vMSI 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