Search and replace #N/A with null

J

Jim

I wish to replace all cells containing #N/A (a value
returned by VLOOKUP) with a null string. I entered #N/A
and "#N/A" in the search field but in both cases I got a
message stating "Microsoft Excel cannot find the data
you're searching for." Any ideas?
 
P

Peo Sjoblom

Select the range, press F5, click special, assuming they are from formulas
check formulas, uncheck everything but errors, click OK
delete the formula from the formula bar and press ctrl + enter

Now this will replace the whole formula with an empty cell
If you want to avoid this directly you can replace the formula with

=IF(ISNA(MATCH(A1,B1:B100,0)),"",VLOOKUP(A1,B1:D100,3,0))



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
P

Peo Sjoblom

You can actually just press delete when you have select the errors

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replace #N/A with 0 1
#N/A 5
vlookup - replace #N/A with text - Excel 2003 2
Replace #N/A 2
How come it say "Microsoft Excel cannot find any data to replace"? 4
working with #N/A and conditional formating 1
Remove #n/a in vlookup to sum results 6
If 2 columns have #N/A 2

Top