1. ## 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. ## 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. ## Re: VlookUp #NA (2003)

Thanks bunch Hanks,

Indra

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

5. ## Re: VlookUp #NA (2003)

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

Steve

6. ## 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))

7. ## 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. ## Re: VlookUp #NA (2003)

Rudi,

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

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

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