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

Thread: VLOOKUP #N/A

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

    Default

    I am working with a VLOOKUP formula. How can I subsitute the #N/A into a 0?

    I don't want the results to say #N/A if it does not find the value. I want it to show 0.

    Any help would be appreciated!!
    Thanks in advanced!!

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

    Default

    On 2002-03-22 11:53, RICK_D wrote:
    I am working with a VLOOKUP formula. How can I subsitute the #N/A into a 0?

    I don't want the results to say #N/A if it does not find the value. I want it to show 0.

    Any help would be appreciated!!
    Thanks in advanced!!
    If your VLOOKUP formula looks like

    =VLOOKUP(A1,$E$2:$G$40,2,FALSE)

    use:

    =IF(COUNTIF($E$2:$E$40,A2),VLOOKUP(A1,$E$2:$G$40,2,0),0)

    If your VLOOKUP formula looks like

    =VLOOKUP(A1,$E$2:$G$40,2,TRUE)

    or

    If your VLOOKUP formula looks like

    =VLOOKUP(A1,$E$2:$G$40,2)

    use:

    =IF(ISNUMBER(MATCH(A2,$E$2:$E$40)),=VLOOKUP(A1,$E$2:$G$40,2),0)




  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    What formula are you using?

    Aladin made excellent recommendations just a little earlier.

    - slightly edited -

    =IF(AND(LEN(A2),COUNTIF(XXXX,A2)),VLOOKUP(A2,XXXXY,2,0),0)

    In English, if A2 is blank and A2 is not in range named XXXX, 0.

    Otherwise lookup A2 in Database XXXXY

    Credit to Aladin.
    Please read his comments in Thread(s) on
    Vlookup.





    Aladin

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or if you dont want to supress your '0's on your worksheet, maby something like this. =IF(COUNTIF($E$2:$E$40,A2),VLOOKUP(A2,$E$2:$G$40,2,0)," ")

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 11:53, RICK_D wrote:
    I am working with a VLOOKUP formula. How can I subsitute the #N/A into a 0?

    I don't want the results to say #N/A if it does not find the value. I want it to show 0.

    Any help would be appreciated!!
    Thanks in advanced!!
    Return your VLOOKUP results to a hidden column and use an IF function to test for presence of #N/A.

    =IF(ISNA(A2),0,A2)

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
  •