Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

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

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

    Default

    How about posting the formula that is giving you an error!

  2. #12
    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:39, Aladin Akyurek wrote:
    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.
    It's all a matter of right-sizing your application. If one is performing that many lookups perhaps it's time to consider a database. Excel's Get External Data capabilities are extraordinary!

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

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

    Default

    Here's the formula

    =IF(ISNA(B8,display2002,2,FALSE),0,(B8,display2002,2,FALSE))

    and I'll try the count if too. Thanks

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

    Default

    Mark:
    I think you are absolutely right! For an average Excel user, I don't believe the memory or the size are the issue in most cases -- it is the ease of use, and understanding of what is going on is perhaps more important to the average user. Talking about the use by experts for sophisticated memory intensive, calc-intensive applications is different.

    Regards!

    Yogi Anand

  5. #15
    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 agree, in the words of my boss "Get it Done".

    I use excel everyday, but I am not a pro like most of you. So I guess I am average and as long as the formulas and macros get the job done, my boss is happy and so am I.

  6. #16
    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:48, Parra wrote:
    Here's the formula

    =IF(ISNA(B8,display2002,2,FALSE),0,(B8,display2002,2,FALSE))

    and I'll try the count if too. Thanks
    Your structure of the formula incorporating the use of ISNA function is right, you may want to look into your_vlookup_formula.


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

    Default

    Thanks for the help Yogi, I have to run of to school, I try it tomorrow.

    Thanks to everyone else for your help.

    Parra

  8. #18
    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:48, Parra wrote:
    Here's the formula

    =IF(ISNA(B8,display2002,2,FALSE),0,(B8,display2002,2,FALSE))

    and I'll try the count if too. Thanks
    I think you forgat the VLOOKUPs in the formula. I assume display2002 is your lookup table. In order to use the COUNTIF version, you need to refer to its first column. Lets say that display2002 refers D2:E400 -- the first column range is then D2:D400. Following the example, you'd have

    =IF(COUNTIF(D2:D400,B8),VLOOKUP(B8,display2000,2,0),0)

    Note. 0 means the same thing to VLOOKUP as FALSE.

    I don't like insisting but the above is much more efficient than

    =IF(ISNA(VLOOKUP(B8,display2000,2,0)),0,VLOOKUP(B8,display2000,2,0))

    Aladin


  9. #19
    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:48, Parra wrote:
    Here's the formula

    =IF(ISNA(B8,display2002,2,FALSE),0,(B8,display2002,2,FALSE))

    and I'll try the count if too. Thanks
    Hi Aladin:
    You are right, he is missing the VLOOKUP in both instances ... so the formula should read ...

    =IF(ISNA(VLOOKUP(B8,display2002,2,FALSE),0,VLOOKUP(B8,display2002,2,FALSE))


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

    Default

    Thank you to everyone that helped me with this. I guess I was looking to do it a certain way when there were easier ways to do it. I ended up doing a SUMIF functions.

    Aladin, Yogi Thanks

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
  •