Eliminate #N/A

[BX]

Hi
I'm using the match function, and when I can't find a name in the range the formulas looking in it gives a #N/A.
Is there any way to use an if statement so if the match function can't find a "match" it gives the value 0.1?
Thanks,
BX

 

[Zack Barresse]

yup, why don't ya post your formula.

 

[Iridium]

Check out the ISNA function in the xl help

HTH

 

[BX]

Formula:
=match(A1,B2:B50,0)
But say the value in A1 isn't found in range B2:B50, I get #N/A.
Can I include an if statement to change any time I get #N/A it appears as a value (0.1).
Thxs

 

[Zack Barresse]

=if(=match(A1,B2:B50,0)="#N/A",0.1,=match(A1,B2:B50,0))
try this.
i'm sure there's a better way w/ iridiums isna.

 

[Iridium]

=if(=match(A1,B2:B50,0)="#N/A",0.1,=match(A1,B2:B50,0))
try this.
i'm sure there's a better way w/ iridiums isna.

Probably the same effect though I haven't tested it. Just thinking along the lines of

=IF(ISNA(formula),0.1,formula)

though might be a brain fart on my part! Need to sleep but can't!

 

[Todd Bardoni]

=IF(ISNA(MATCH(A1,B2:B50,0)),0.1,MATCH(A1,B2:B50,0))

 

[Zack Barresse]

oh i hear ya iridium, same here (last night for me - this morning for you!)