ISNA question

[excel_2009]

hi excel gurus,

I've got the following formula but I keep getting an error when i attempt to use it :

=(ISNA(MATCH("*"&LEFT(B2,10)&"*",E2,0),"0",MATCH("*"&LEFT(B2,10)

what is wrong with the formula??

Thank you

 

[prajul89]

I just don't know what you want.
why is there multiple arguments in ISNA??
I should contain only one argument.

 

[excel_2009]

It's very complex to explain (especially as im a novice!)

here is the original formula, i simply want to add an 0 if the results are #NA

=MATCH("*"&LEFT(B2,10)&"*",E2,0)

 

[prajul89]

=IF(ISNA(MATCH("*"&LEFT(B2,10)&"*",E2,0)),0,MATCH("*"&LEFT(B2,10)&"*",E2,0))

I hope this will help

 

[Aladin Akyurek]

hi excel gurus,

I've got the following formula but I keep getting an error when i attempt to use it :

=(ISNA(MATCH("*"&LEFT(B2,10)&"*",E2,0),"0",MATCH("*"&LEFT(B2,10)

what is wrong with the formula??

Thank you

Try...

=IF(ISNA(MATCH("*"&LEFT(B2,10)&"*",E2,0)),0,MATCH("*"&LEFT(B2,10),E2,0))

Better because MATCH returns a number when successful...

=IF(ISNUMBER(MATCH("*"&LEFT(B2,10)&"*",E2,0)),MATCH("*"&LEFT(B2,10),E2,0),0)

On Excel 2007 or later...

=IFERROR(MATCH("*"&LEFT(B2,10)&"*",E2,0),0)

 

[excel_2009]

Thank you!!! all the formulas work, thank you once again