PDA

View Full Version : Solved: vlookup and the value #N/A



brunocaccio
12-07-2005, 07:37 AM
Good morning everyboody!

I use the formulas "vlookup" or "hlookup" with the parameter "false".
When there is the value #N/A, I would like that it is replaced by other string of character.
For to make it, I have used the conditional "if" with the following way:
"if(vlookup(*,*,*,false)="#N/A","...","...")".
It dosen't work. It still displays "#N/A".
Does anyone know how to make?

mvidas
12-07-2005, 08:01 AM
Hi brunocaccio,

Welcome to vbax!

You're on the right track, just a little different syntax.
=IF(ISERROR(vlookup(*,*,*,false),"...",vlookup(*,*,*,false))

Matt

brunocaccio
12-07-2005, 08:11 AM
Thank you very much!

mvidas
12-07-2005, 08:27 AM
Glad to help!

And for reference, you can mark it the whole title solved by going to Thread Tools at the top of the thread, and choosing "Mark Thread Solved". I'll take care of it on this one (I saw the [SOLVED] above) :)

Let me know if you need anything else! I believe there is also an ISNA function, which could let you separate the error if there are more (and you want the specificity). But ISERROR works for me every time :)
Matt

shades
12-07-2005, 08:34 AM
Just as a note:

ISERROR - Returns TRUE if the cell contains any error value (#N/A, #VALUE!, #REF!, # DIV/0!, #NUM!, #NAME?, #NULL!)

ISERR - Returns TRUE if the cell contains any error value except #N/A

ISNA - Returns TRUE if teh cell contains the #N/A error value