VlookUp #NA (2003)

Topic

New Reply

Hello,

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

thanks and regards

Indra

Reply | Quote

Replies

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.

Reply | Quote

Thanks bunch Hanks,

Indra

Reply | Quote

Either Indra confused Hans with (Tom) Hanks or meant that Hans is a Hunk…
I wonder which one is #NA

Reply | Quote

Perhaps Indra thought both….

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Rudi,

it’s been minutes, I am still laughing…

Reply | Quote

W00t!! – Who said learning can’t be fun!

Reply | Quote

[indent]

---

Who said learning can’t be fun

---

[/indent]

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

Though I didn’t beleive a lot of what he said…

Steve

Reply | Quote