Is there a way to make all the #N/As in a spreadsheet disappear?

I normally get the #NA Error from the VLookup Function. Use the "IF"
and "ISNA" functions to replace the error with some usable text. The
formula below will search Column A and return the value in the second
column if it finds a match for the value in cell C1 or the text "Not
Found" if there is no match.

=IF(ISNA(VLOOKUP(C2,$A$2:$B$6,2,FALSE)),"Not Found",
VLOOKUP(C2,$A$2:$B$6,2,FALSE))

The logical test of the IF statement is looking for the #NA error from
the VLOOKUP formula. If if returns the #NA error, the IF formula is
true and the true part is processed. If the error is not found, the
false part of the IF statement is processed, which just happens to be
the very same VLOOKUP formula.

The easiest I have found to make this work is to get the VLOOKUP
function working first then add the IF and ISNA formulas. That way I
know I do not have an issue the VLOOKUP formula.




amirstal wrote:
> Is there a way to make all the #N/As in a spreadsheet disappear?

This is the actual formula I have:
=if(ac23="a",u23*vlookup(h23,rates!$a$1:$d$14,4),if(ac23="b",u23/vlookup(h23,rates!$a$1:$d$14,4),if(ac23="c",u23/vlookup(h23,rates!$a$1:$d$14,4))))
But I could not insert the ISNA in so it will work.
Can you help?

Ok. It did work.
But now I got read of the #N/A and instead got #VALUE!.
How do I get rid of #VALUE!?


Thanks

(E-Mail Removed) wrote:
> I normally get the #NA Error from the VLookup Function. Use the "IF"
> and "ISNA" functions to replace the error with some usable text. The
> formula below will search Column A and return the value in the second
> column if it finds a match for the value in cell C1 or the text "Not
> Found" if there is no match.
>
> =IF(ISNA(VLOOKUP(C2,$A$2:$B$6,2,FALSE)),"Not Found",
> VLOOKUP(C2,$A$2:$B$6,2,FALSE))
>
> The logical test of the IF statement is looking for the #NA error from
> the VLOOKUP formula. If if returns the #NA error, the IF formula is
> true and the true part is processed. If the error is not found, the
> false part of the IF statement is processed, which just happens to be
> the very same VLOOKUP formula.
>
> The easiest I have found to make this work is to get the VLOOKUP
> function working first then add the IF and ISNA formulas. That way I
> know I do not have an issue the VLOOKUP formula.
>
>
>
>
> amirstal wrote:
> > Is there a way to make all the #N/As in a spreadsheet disappear?

Add the ISNA trap to all cells with this macro.

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

On 11 Dec 2006 06:46:34 -0800, "amirstal" <(E-Mail Removed)> wrote:

>This is the actual formula I have:
>=if(ac23="a",u23*vlookup(h23,rates!$a$1:$d$14,4),if(ac23="b",u23/vlookup(h23,rates!$a$1:$d$14,4),if(ac23="c",u23/vlookup(h23,rates!$a$1:$d$14,4))))
>But I could not insert the ISNA in so it will work.
>Can you help?