MATCH and #N/A

[Bill]

I am searching 12 columns to find the occurance of a particular letter. If
the letter is not in any of the columns, MATCH returns #N/A. How do I set up
the formula to return zero instead of #N/A?

 

[Dave Peterson]

=if(isnumber(match(...)), match(...),0)
or
=if(isna(match(...)),0,match(...))

if you're using xl2007, you may want to look at =iferror().

 

[Peo Sjoblom]

=IF(ISNA(MATCH(lookup,Range,0)),0,MATCH(lookup,Range,0))

--


Regards,


Peo Sjoblom

 

[PCLIVE]

One Way:

=IF(ISNA(MATCH(E1,A1:A10,"ValueToSearch")),0,MATCH(E1,A1:A10,"ValueToSearch"))

HTH,
Paul

 

[David Biddulph]

=IF(ISNA(MATCH(...)),0,MATCH(...))