Results 1 to 6 of 6

VLookup getting rid of #N/A

This is a discussion on VLookup getting rid of #N/A within the Excel Questions forums, part of the Question Forums category; Hi, this is my first post so excuse me if it sounds like a newb question. I used this tip ...

  1. #1
    New Member
    Join Date
    Jun 2003
    Posts
    18

    Default VLookup getting rid of #N/A

    Hi,
    this is my first post so excuse me if it sounds like a newb question.

    I used this tip to help me get rid of the #N/A I was getting.

    http://www.mrexcel.com/td0110.html

    However, now I do NOT want an exact match to my lookup value, is there a way to use this statement without having to find an exact match?

    IF(COUNTIF($A$1:$A$10,"cat"),VLOOKUP("cat",$A$1:$C$10,3,0),"no match")

  2. #2
    Board Regular cheveley's Avatar
    Join Date
    Sep 2002
    Location
    Jozie
    Posts
    273

    Default Re: VLookup getting rid of #N/A

    I generally use the the isna() formula if there is nothing to look up and dont wont the error message returned, but a blank cell.

    i.e. =if(isna(vlookup(x,x,x,false)),"",vlookup(x,x,x,false))

  3. #3
    New Member
    Join Date
    Jun 2003
    Posts
    18

    Default Re: VLookup getting rid of #N/A

    Well, there are things to lookup, but I wanted to make this a template, and did not want #N/A's all over. Also, I am using it for an automatic download for a fellow employee.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: VLookup getting rid of #N/A

    Quote Originally Posted by 4ank
    Hi,
    this is my first post so excuse me if it sounds like a newb question.

    I used this tip to help me get rid of the #N/A I was getting.

    http://www.mrexcel.com/td0110.html

    However, now I do NOT want an exact match to my lookup value, is there a way to use this statement without having to find an exact match?

    IF(COUNTIF($A$1:$A$10,"cat"),VLOOKUP("cat",$A$1:$C$10,3,0),"no match")
    The only way you can get #N/A with an approximate match VLOOKUP is to search for a value that is less than the 1st entry in the table_array. To alleviate this problem simply set the leftmost column of the 1st table_array record to ="" as shown below. Fix your table not the formula!!!

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    1
    *
    **
    *****
    2
    cat*******
    3
    dog*******
    4
    mouse*******
    5
    ********
    6
    ********
    7
    ********
    8
    ********
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  5. #5
    New Member
    Join Date
    Jun 2003
    Posts
    18

    Default Re: VLookup getting rid of #N/A

    Thank you both for your replies. I got it working thanks to you. I appreciate the quick response. This forum seems like a very helpful and useful tool!

    Thanks again, I am sure you will hear some more from me.

  6. #6
    Board Regular
    Join Date
    Mar 2010
    Posts
    60

    Default Re: VLookup getting rid of #N/A

    Quote Originally Posted by Mark W. View Post
    The only way you can get #N/A with an approximate match VLOOKUP is to search for a value that is less than the 1st entry in the table_array. To alleviate this problem simply set the leftmost column of the 1st table_array record to ="" as shown below. Fix your table not the formula!!!


    This is awesome! MUCH Simpler than any other way I have found.

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