Results 1 to 4 of 4

Thread: Lookup, If, or Other Function?

  1. #1

    Lookup, If, or Other Function?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    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;;;@


  3. #3
    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?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    Yeah, you can say, =IF(VLOOKUP(...)="","whatever",VLOOKUP(...))


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •