Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    GETTING RID OF #N/A resulting from a VLOOKUP FORMULA

    I am using a vlookup formula and in some cases there is no match in the Table Array from the Lookup Value. Is there a way that I can have it show a 0 or a BLANK instead of a #N/A.

    I would like to sum the column I am using the vlookup formula on.

    Thank you.

    MNN

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    Enclose the VLookup in an IFError() function. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Use RG's suggestion, or:

    Instead of =SUM([range]) use =SUMIF([range],"<>#N/A") or =SUMIF([range],"<1E100").

    Both work: the first one just ignores #N/A errors, the second one ignores all errors.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To expand on RetiredGeek's comment. If you have XL2007 or higher you can use something like:

    =IFERROR(VLOOKUP(....),0)

    with earlier versions (which did not have IFERROR function), it could be done with:
    =IF(ISERROR(VLOOKUP(....)),0,VLOOKUP(....))

    You can replace the zero (0) with a null string (""") if you want the result to appear blank. [It can not be blank, since the cell has a formula in it and so can not be seen as blank.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks. Worked great!

  6. #6
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    I use

    =if(isna(vlookup(....)),"",vlookup(....))


    Alan

Posting Permissions

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