Find and Replace Lookup errors

[Guest]

I'm trying to replace two error messages (#N/A and #VALUE!) found throughout
my spreadsheet. The error messages are a result of lookups that cannot find
values on my look up tables. I would like those cells to show up as blank
for reporting purposes.

I tried to do a find and replace. I did a find ...look in...values...find
all. The cells were all found. I leave the replace box blank and click on
replace all and get an error message. After trying several times, I find
that the "look in" box on the replace page no longer has values highlighted
and fact values doesn't even show up.

How can I replace all the look up error messages with blank cells?

 

[Peo Sjoblom]

Change your formula to

=IF(ISNA(VLOOKUP()),"",VLOOKUP())

obviously put your real data in there


with regards to the value error, that is not because of the lookup, it's
because somewhere you are trying to calculate with text. You need to find
that and correct it. Press F5, special and select formulas and deselect
everything but errors, that will select all formulas with errors

 

[Pete_UK]

You can do it within your formulae, testing to see if an error results
and returning something else if it does. This kind of construct:

=IF(ISNA(your_lookup),"",your_lookup)
or
=IF(ISERROR(your_formula),"",your_formula)

The first one only traps the #NA error (which is what lookup returns
if there is no match), whereas the second will trap all errors. If you
are getting a #VALUE error, though, you are probably trying to perform
some arithmetic on a text value (maybe returned from your lookup, so
the lookup itself is not necessarily at fault).

Hope this helps.

Pete

 

[Guest]

Thank you so much - worked great! Being a bit of a novice, showing me the
extra closed parenthesis was especially helpful.

Have a great day!

 

[Guest]

If I could bother you with one more question, please. As I said in my last
post, your answer worked perfectly for me. Now, I need to know what to do
when my lookup puts a zero (0) in a cell when the lookup found a blank cell.
I would like it to be blank if the lookup found a blank cell. Can you help
me with that too?--
Julie

 

[Guest]

Try this:

=IF(COUNTIF(A2:A100,A1),SUBSTITUTE(VLOOKUP(A1,A2:B100,2,0),0,""),"")

 

[a7n9]

Use ISNA and IF to find out if the formula is resulting in an error.

For example:
=IF(ISERROR(VLOOKUP(D10,E6:E10,1,0)),"",VLOOKUP(D10,E6:E10,1,0))

In Excel 2007, there is a function IFERROR, which eliminates the need of
typing the formula again.

 

[Peo Sjoblom]

Can the lookup return a zero deriving from a zero in the lookup column, if
not you can simply use a custom format to hide the zeros

General;-General;

or


0.00;-0.00;

can of course be done with currency format as well



will not display zeros

that way you don't have to change the formulas