Alternative for Vlookup output of #N/A if data not found?

[mcmilja]

Hello,

Is there an alternative for the Vlookup function output of #N/A when data is
not found? The reason I ask is because I need to use an If function based on
the Vlookup output if the data is not found such as =IF(K2="#N/A","SPARE",)
but the function does not recognize #N/A.

Thanks!
Jaret

 

[Dave Peterson]

=if(isna(k2),"it's an n/a error","it's not an n/a error")

But you can do the same kind of thing in your =vlookup() formula:

=if(isna(vlookup(...)),"it's an error",vlookup(...))

And if you're using xl2007, you can look at =iferror() in excel's help.

 

[David Biddulph]

Your formula is looking for the text string #N/A (the quote marks tell it to
look for a text string).

Try =IF(ISNA(K2),"SPARE",)

 

[mcmilja]

Thank You!

Your formula is looking for the text string #N/A (the quote marks tell it to
look for a text string).

Try =IF(ISNA(K2),"SPARE",)

 

[mcmilja]

Thanks! This did the trick...

=if(isna(k2),"it's an n/a error","it's not an n/a error")

But you can do the same kind of thing in your =vlookup() formula:

=if(isna(vlookup(...)),"it's an error",vlookup(...))

And if you're using xl2007, you can look at =iferror() in excel's help.