how to automatic replace #N/A with text ?

  • Thread starter Thread starter quietning
  • Start date Start date
Q

quietning

I am using vlookup(),sometimes it return "#N/A",which makes a terrible
look.
I know why it return "#N/A".But I want to use "" or any text to replace
"#N/A".
I tried many ways,but they didn't work at all.
What can i do? Any one please help.
 
quietning, use something like this,
=IF(B4="","",VLOOKUP(B4,mytable,2,FALSE))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
A couple of ways,

=IF(ISNA(MATCH(A2,B2:B100,0)),"",VLOOKUP(A2,B2:C100,2,0))

or

=IF(ISNA(VLOOKUP(A2,B2:C100,2,0)),"",VLOOKUP(A2,B2:C100,2,0))


if the table is really big the first formula might be a bit faster

--
Regards,

Peo Sjoblom

(No private emails please)
 
Peo Sjoblom

Thank you very much, I tried your suggestion and it works!

Paul B

I know what you mean,but if b4 not a "" and can not be found in
mytable?
In some situation it works,but not for me.
Thanks any way.
 
Back
Top