Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Ignore #N/A Value with Vlookup???

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am doing a vlookup function and I sum the results. But how do I get the vlookup function to display "0" if there is no value for what it is looking for. I was trying to do a if function but it didn't work. Any suggestions. Thanks

  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-04-24 16:21, Parra wrote:
    I am doing a vlookup function and I sum the results. But how do I get the vlookup function to display "0" if there is no value for what it is looking for. I was trying to do a if function but it didn't work. Any suggestions. Thanks
    Keep #N/A and use

    =SUMIF(range,"<>#N/A")

    or, use:

    =IF(COUNTIF(B2:B100,A1),VLOOKUP(A1,B2:G100,2),0)

    to return 0 instead of #N/A when appropriate.

    Aladin

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-24 16:21, Parra wrote:
    I am doing a vlookup function and I sum the results. But how do I get the vlookup function to display "0" if there is no value for what it is looking for. I was trying to do a if function but it didn't work. Any suggestions. Thanks
    Use the following

    =IF(ISNA(your_vlookup_formula),0,(your_vlookup_formula))

    HTH


    _________________
    Yogi Anand
    Edit: Deleted reference to inactive web site from signature line

    [ This Message was edited by: Yogi Anand on 2003-01-19 18:34 ]

  4. #4
    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

    Or, have an additional column containing the formula, =IF(ISNA(A1),0,A1), where A1 contains your VLOOKUP. You may choose to hide column A so that viewers of the worksheet are oblivious to the presence of #N/A errors. I believe this is preferrable to a formula that performs your VLOOKUP (or otherwise examines your table array) twice!

    [ This Message was edited by: Mark W. on 2002-04-24 16:50 ]

  5. #5
    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-04-24 16:31, Mark W. wrote:
    Or, have and additional column containing the formula, =IF(ISNA(A1),0,A1), where A1 contains your VLOOKUP. You may choose to hide column A so that viewers of the worksheet are oblivious the the presence of #N/A errors.
    I bet COUNTIF will be as fast as that & will not waste space/memory.

  6. #6
    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-04-24 16:34, Aladin Akyurek wrote:
    On 2002-04-24 16:31, Mark W. wrote:
    Or, have and additional column containing the formula, =IF(ISNA(A1),0,A1), where A1 contains your VLOOKUP. You may choose to hide column A so that viewers of the worksheet are oblivious the the presence of #N/A errors.
    I bet COUNTIF will be as fast as that & will not waste space/memory.
    Time vs. Size -- the classic worksheet design tradeoff! It all depends on the size of the table array and the average successful hit rate. COUNTIFs can be painfully slow.

    My preferred design is not to perform an exact match lookup. Retrieve the results and compare it against the original lookup value. This can be quite a time saver if the hit rate is low.

    [ This Message was edited by: Mark W. on 2002-04-24 16:41 ]

  7. #7
    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-04-24 16:36, Mark W. wrote:
    On 2002-04-24 16:34, Aladin Akyurek wrote:
    On 2002-04-24 16:31, Mark W. wrote:
    Or, have and additional column containing the formula, =IF(ISNA(A1),0,A1), where A1 contains your VLOOKUP. You may choose to hide column A so that viewers of the worksheet are oblivious the the presence of #N/A errors.
    I bet COUNTIF will be as fast as that & will not waste space/memory.
    Time vs. Size -- the classic worksheet design tradeoff! It all depends on the size of the table array and the average successful hit rate.
    My fear is that you're not trading off size/memory against time: you're spending memory.

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would to use Yogi's formula because I feel I won't have to do much but I get an error message, any suggestions?

    Thanks

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe it will help if I put the vlookup formula.

    =VLOOKUP(B8,display2002,2,FALSE)

  10. #10
    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-04-24 16:44, Parra wrote:
    I would to use Yogi's formula because I feel I won't have to do much but I get an error message, any suggestions?

    Thanks
    Give a try to the COUNTIF version too.

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
  •