Vlookup - how do I change "#N/A" into "0" or blank?

  • Thread starter Thread starter Wes
  • Start date Start date
W

Wes

I am doing Vlookup and in some cases the variable I am
looking up does not exist in the referenced cells - which
is correct. The formula returns "#N/A" because it can't
find a match. When that happens, I'd like to turn
the "N/A" into either a blank or the number 0. I've tried
using the IF function:
if(vlookup(...)="#N/A",0,vlookup(...)), but the cell keeps
showing #N/A. I've tried "", #N/A (without the quotes),
etc., but all continue to give me #N/A in the cell.

For now I have to go through the results and look for all
#N/A, and set them to 0.

Any suggestions?

Thanks!
 
If you don't mind fixing your vlookup then highlight the vlookup column.
Copy/Paste special/values.

Then edit/replace

Type in #N/A

Replace with 0 or blank

Loses your formula though...
 
Back
Top