Reply With QuoteWhen Vlookup finds a match, and the corresponding cell to get is blank, it actually returns a 0, which when formatted as date returns, 1/1/1900 as you have noted.
Try formatting the cell as Number|Custom with Type: m/d/yyyy;;;@
I am trying to write a formula in B2. I want it to look at A1, and if the text in A1 matches any cell in range A2:A5, I want it to return the corresponding value in column B.
Example: I type Cat in A2. I want B2 to return B3's value ("Mammal") because Cat is found in the range A2:A5. I tried using a lookup formula, but whenever it was supposed to return a blank cell, it instead returned 1/1/1990!! Do I need some type of nested formula so that if the cell is blank, it returns nothing?
A B D 1 Animal Classification Last Seen 2 Cat 3 Cat Mammal 4/3/2013 4 Frog Amphibian 5 Aligator Reptile 5/6/2014
When Vlookup finds a match, and the corresponding cell to get is blank, it actually returns a 0, which when formatted as date returns, 1/1/1900 as you have noted.
Try formatting the cell as Number|Custom with Type: m/d/yyyy;;;@
Is there a way to have it not return anything if the cell is blank? This formula is being used in many locations, and I really didn't want to have to pick out individual cells and change their formatting. It also syncs to another sheet, so even if I change the format of certain cells, I'll have to change the format of other cells that are linked to this cell.
Do I make sense?
Yeah, you can say, =IF(VLOOKUP(...)="","whatever",VLOOKUP(...))
Bookmarks