Lookup, If, or Other Function?

MWhite17

New member
Joined
Jul 24, 2014
Messages
2
Reaction score
0
Points
0
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?


ABD
1AnimalClassification Last Seen
2Cat
3CatMammal4/3/2013
4FrogAmphibian
5AligatorReptile5/6/2014
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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;;;@
 

MWhite17

New member
Joined
Jul 24, 2014
Messages
2
Reaction score
0
Points
0
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? :)
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Yeah, you can say, =IF(VLOOKUP(...)="","whatever",VLOOKUP(...))
 
Top