Hide #Value! when error

**AmazingTrans** · 03-23-2009, 02:48 AM

Hi there,
I have a sheet with lots of VLOOKUP. When the VLOOKUP cannot find the specific items, my cells will display #VALUE!. and this will be shown even when I print.
I am wondering if there is any options that I can check off, or do not check error so that the #VALUE! do not display? Of course I am looking for another options other than adding If statements in front of the formula.

I have tried conditioning format, if formula = ISERROR(#VALUE!) cell will be white. But this will affect cells that have just normal text as well.

Any opinions?

I really hope someone can help me with this.

**AmazingTrans** · 03-23-2009, 02:51 AM

OR is there anyway instead of displaying #VALUE!, it will display a dash? I remember I use to do this 10 years ago, but now i don't remember anymore.

**zbor** · 03-23-2009, 03:02 AM

How about this..
=IFERROR(VLOOKUP(A13;C4:D9;2;FALSE);"-")

**JBeaucaire** · 03-23-2009, 03:08 AM

Originally Posted by zbor

How about this..
=IFERROR(VLOOKUP(A13;C4:D9;2;FALSE);"")

The OP is using Excel 2003. Watch for that when suggesting using functions only native to 2007.

**DonkeyOte** · 03-23-2009, 03:29 AM

I think the OP in the initial thread stipulates that they are looking for methods other than amending the formulae:

Of course I am looking for another options other than adding If statements in front of the formula

in which case as OP states Conditional Formatting would work:

=ISERROR(cell reference)
formatted to white

This will not affect non-error cells, however, be warned however that CF is Volatile... altering the underlying formulae would be preferable on this basis - and using native formulae would be preferable to UDF.

The fact you're getting a #VALUE! error would also imply to me that the issue is not that the VLOOKUP can not find the result rather it is that the result of the VLOOKUP is non-numeric and you're are attempting some subsequent numeric coercion, can you post your formula ?

**JBeaucaire** · 03-23-2009, 03:07 AM

Simplest way is to wrap your existing VLOOKUP formula in an ISERROR test, but that means for it to bring back a value, every single one of those formulas has to be run TWICE...ugh. It looks like this for example:

=IF(ISERROR(VLOOKUP(A1,B1:C10,2,FALSE)),"-",VLOOKUP(A1,B1:C10,2,FALSE))

If you don't mind that, then just use that technique.

If you're using other macros in your sheet and/or you don't mind adding a new function into your sheet, there is a simpler formula, but the end result will be exactly the same. Using it would mean macros would have to be active which is why I mentioned it.

To do this:
Press Alt-F11 to open the VBEditor
Click on Insert > Module to open a standard module window
Paste in this code:

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function

Press Alt-F11 to close the editor
Save your sheet.

Now you can use a simple "once through" formula like this;

=IFERROR(VLOOKUP(A1,B1:C10,2,FALSE),"-")