Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VLOOKUP

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've seen it before
    How do I not return a #NA in a vlookup statement? Please direct me to the archived answer

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-25 13:17, Mike wrote:
    I've seen it before
    How do I not return a #NA in a vlookup statement? Please direct me to the archived answer
    Mike,

    Would take eons before I find one of the many VLOOKUP formulas, whence...

    If you need an exact match, use:

    =IF(COUNTIF(A1:A7,lookup-value),VLOOKUP(lookup-value,A1:D7,2,0),0)

    Otherwise use:

    =IF(ISNUMBER(MATCH(lookup-value,A1:A7)),VLOOKUP(lookup-value,A1:D7,2),0)

    Both return 0 instead of #N/A.

    A1:D7 is the lookup-table, whose first column is A1:A7.

    Aladin

  3. #3
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if it's always the same value (eg. "0") that causes the #N/A could you add it to your lookup table? an example of what i mean is at http://www.mrexcel.com/wwwboard/messages/21090.html and responses

    [ This Message was edited by: anno on 2002-02-25 15:08 ]

  5. #5
    New Member
    Join Date
    Jun 2002
    Location
    Connecticut
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF(ISERROR(VLOOKUP(a1,$b$7:$C$12,2,0)=TRUE),"no error",VLOOKUP(a1,$b$7:$C$12,2,0))

    Try this above.

    Best of luck

Some videos you may like

User Tag List

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
  •