Solved VLOOKUP returns 00/01/1900 instead of blank

February 15, 2017 at 00:23:17
Specs: Windows 7
Hi, this is my first post so thanks in advance for any assistance.
I am using a formula =VLOOKUP($E15,SNOWEMEA,9,FALSE) to pull back a date if there is one present, some of the cells in 'SNOWEMEA' table are blank but when the cell is populated it displays 00/01/1900. Is there a way to make this blank by amending the above formula?

Many thanks

message edited by Sirobi


See More: VLOOKUP returns 00/01/1900 instead of blank

Report •

#1
February 15, 2017 at 00:30:26
✔ Best Answer
Here is one way of doing it, but others may be able to suggest an alternative

=If(VLOOKUP($E15,SNOWEMEA,9,FALSE)="","",VLOOKUP($E15,SNOWEMEA,9,FALSE))


Report •

#2
February 15, 2017 at 00:43:23
Hi AlwaysWillingToLearn

Thanks for the speedy response, I have applied the formula and it returns a 'TRUE' instead of a valid date or blank.

Any other ideas?


Report •

#3
February 15, 2017 at 00:51:54
That's very strange, iv just checked it again and it works absolutely fine for me. Is it returning a TRUE for everything or just for the cells that are blank?

Report •

Related Solutions

#4
February 15, 2017 at 01:04:38
Hi AlwaysWillingToLearn

My bad! I have adjusted the formula and it works 100%

A big thank you for your help, its saved me a huge headache

all the best


Report •

#5
February 15, 2017 at 03:20:56
Phew! glad its working I was trying to figure out what was going on. When you say you adjusted the formula can you tell us what you adjusted please? this will help others that visit this site and hopefully help me in case I made an error.

Thanks,


Report •

#6
February 15, 2017 at 06:52:14
The only thing I might add is that I like to save keystrokes by using 0 and 1 as the range_lookup argument.

=If(VLOOKUP($E15,SNOWEMEA,9,0)="","",VLOOKUP($E15,SNOWEMEA,9,0))

That is more of a personal preference when applied specifically to VLOOKUP, but it also gives some insight into the inner workings of Excel. In many cases, the binary of equivalent of TRUE (1) and FALSE(0) can be used, allowing for more flexibility.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question