Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VlookUp #NA (2003)

    Hello,

    I have VlookUp, but need to replace #NA with 0 in same cell. Is it possible?

    thanks and regards

    Indra

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VlookUp #NA (2003)

    Let's say that you have a formula

    =VLOOKUP(a, b, c, d)

    that may return #N/A. You can change the formula to

    =IF(ISNA(VLOOKUP(a, b, c, d)), 0, VLOOKUP(a, b, c, d))

    The ISNA(...) function returns TRUE if ... evaluates to #N/A, and FALSE otherwise. So if VLOOKUP returns #N/A, ISNA returns TRUE, and IF returns 0. If VLOOKUP returns a valid result, ISNA is FALSE, and IF returns the value of VLOOKUP.

    You can replace 0 with whatever you want to return if VLOOKUP results in #N/A, for example an empty string "", or anything you like.

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VlookUp #NA (2003)

    Thanks bunch Hanks,

    Indra

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VlookUp #NA (2003)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Either Indra confused Hans with (Tom) Hanks or meant that Hans is a Hunk...
    I wonder which one is #NA
    Regards,
    Rudi

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

    Re: VlookUp #NA (2003)

    Perhaps Indra thought both.... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VlookUp #NA (2003)

    Perhaps this formula would tell us:
    =IF(ISNA(VLOOKUP("Hans", HunkTable, 2, False)), "Hans is a Hunk", VLOOKUP(Hans, HanksTable, 2, False))
    Regards,
    Rudi

  7. #7
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VlookUp #NA (2003)

    hue.he.he... you guys are great
    at the time I was so hard try to find out - of course with my capacity, untill remember I did post in this forum

    Very sorry ya, Hans.. : (

    cheers

    Indra

  8. #8
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VlookUp #NA (2003)

    Rudi,

    it's been minutes, I am still laughing...

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VlookUp #NA (2003)

    W00t!! - Who said learning can't be fun! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

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

    Re: VlookUp #NA (2003)

    <hr>Who said learning can't be fun<hr>

    I think it was one of my P-Chem professors in college, but I don't know if it was an original sentiment or he was quoting someone else <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Though I didn't beleive a lot of what he said...

    Steve

Posting Permissions

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