Results 1 to 9 of 9

VLOOKUP showing #NA

This is a discussion on VLOOKUP showing #NA within the Excel Questions forums, part of the Question Forums category; Is there a way to keep a cell from showing #NA in the cell where I am performing a VLOOKUP? ...

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Posts
    61

    Default VLOOKUP showing #NA

    Is there a way to keep a cell from showing #NA in the cell where I am performing a VLOOKUP? It seems that there should be a way to make it look cleaner.


    Glisson
    Excel Neophyte
    To Live In Fear Is Not To Live

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,121

    Default Re: VLOOKUP showing #NA

    Try:

    =IF(ISNA(VLOOKUP),"",VLOOKUP))

    You can also use 0 instead of "".

    Hope that helps,

    Smitty

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,217

    Default Re: VLOOKUP showing #NA

    Quote Originally Posted by Glisson
    Is there a way to keep a cell from showing #NA in the cell where I am performing a VLOOKUP? It seems that there should be a way to make it look cleaner.


    Glisson
    Excel Neophyte
    A couple of options...

    1]

    =IF(ISNUMBER(MATCH(A2,$E$2:$E$20,0)),VLOOKUP(A2,$E$2:$G$20,3,0),"")

    2]

    =IF(ISNA(SETV(VLOOKUP(A2,$E$2:$G$20,3,0))),"",GETV())

    3]

    B2:

    =IF(ISNA(C2),"",C2)

    C2:

    =VLOOKUP(A2,$E$2:$G$20,3,0)

    The 2nd and 3rd formulas work faster than the 1st. The 2nd requires the morefunc.xll add-in.
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Board Regular
    Join Date
    Nov 2003
    Posts
    61

    Default Re: VLOOKUP showing #NA

    So that would turn

    =VLOOKUP(B3,Emptable,3,FALSE)

    into what? I'm sorry I tried a couple of variations and couldn't get it to work.


    P.S. When you look at formulas like this do you see it like grafitti as I do or can you actually "read" it like a second ( or third ) language? just curious.

    Glisson
    To Live In Fear Is Not To Live

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,217

    Default Re: VLOOKUP showing #NA

    Quote Originally Posted by Glisson
    So that would turn

    =VLOOKUP(B3,Emptable,3,FALSE)

    into what? I'm sorry I tried a couple of variations and couldn't get it to work.


    P.S. When you look at formulas like this do you see it like grafitti as I do or can you actually "read" it like a second ( or third ) language? just curious.

    Glisson
    1]

    =IF(ISNUMBER(MATCH(B3,INDEX(Emptable,0,1)0)),VLOOKUP(B3,Emptable,3,0),"")

    2]

    =IF(ISNA(SETV(VLOOKUP(B3,Emptable,3,0))),"",GETV())

    3]

    C3:

    =IF(ISNA(D3),"",D3)

    D3:

    =VLOOKUP(B3,Emptable,3,0)

    Note that 0 == FALSE.

  6. #6
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,121

    Default Re: VLOOKUP showing #NA

    P.S. When you look at formulas like this do you see it like grafitti as I do or can you actually "read" it like a second ( or third ) language? just curious.
    You'll find that this is Aladin's PRIMARY language. He is THE Master!

    Smitty

  7. #7
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: VLOOKUP showing #NA

    It looks like the net ate one of Aladin's commas, in solution #1 --

    =IF(ISNUMBER(MATCH(B3,INDEX(Emptable,0,1),0)),VLOOKUP(B3,Emptable,3,0),"")
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  8. #8
    Board Regular
    Join Date
    Nov 2003
    Posts
    61

    Default Re: VLOOKUP showing #NA

    Thanks. That got the result I wanted! Now I can go back and pick apart the formula to try to understand it better so that I can use it in other formulas.

    I'm just starting to learn his Primary language. You guys are so cool to help guys like me. I never really realized HOW much you could do with Excel. A lot of people here are really pushing the "accepted" limits with it. I played Monopoly written in Excel last night! How cool is that!?

    P.S. Is there an ENTRY level book recommended for learning VBA and/or some of these more advanced formulas? I like the books that actually have you work through "projects" to teach you so you can see how to apply the ideas in real world aplications.

    Thanks again,
    Glisson
    To Live In Fear Is Not To Live

  9. #9
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,121

    Default Re: VLOOKUP showing #NA

    John Walkenbach has an Excel VBA for Dummies book. (Although, I've never read it, so I can't tell you how it is).

    He also has several others:

    http://www.j-walk.com/ss/books/index.htm

    Excel Power Programming with VBA is excellent.

    Bill Jelen, AKA Mr. Excel, also has "Mr. Excel on Excel", which is supposed to be very good as well.

    Those books are a start, but don't forget to post anytime you have a question.

    As you've seen, what is Greek to many isn't to the MVP's!

    Hope that helps,

    Smitty

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com