If isna with index match not working in excel 2003

Hi all,

I Know it is very simple task but i dont know why it is not working. i have applied index match function in my desired yellow shaded cell. now to remove N/A i want to bring here, IF ISNA or IF ERROR, but it is not working. Can any one fix that, please. I need to keep Index match formula there.

regards,

Ryan

=if(isna(match(b1,d5:d9,0)),"",index(f5:f9,match(b1,d5:d9,0))) will do as you only have to test the match part
for #n/a
the one you tried has too many () and in the wrong place
=if(isna(index(f5:f9,match(b1,d5:d9,0)),"",(index(f5:f9,match(b1,d5:d9,0)))
should be
=IF(ISNA(INDEX(F5:F9,MATCH(B1,D5:D9,0))),"",INDEX(F5:F9,MATCH(B1,D5:D9,0)))

Thanks. it was easy after your detailed explanation

Why not just use =IFERROR(INDEX(F5:F9,MATCH(B1,D5:D9,0)),"")

Originally Posted by newdoverman

Why not just use =IFERROR(INDEX(F5:F9,MATCH(B1,D5:D9,0)),"")

Because IFERROR() isn't available until Excel 2007, see the thread title.

Originally Posted by martindwilson

=if(isna(match(b1,d5:d9,0)),"",index(f5:f9,match(b1,d5:d9,0))) will do as you only have to test the match part
for #n/a
the one you tried has too many () and in the wrong place
=if(isna(index(f5:f9,match(b1,d5:d9,0)),"",(index(f5:f9,match(b1,d5:d9,0)))
should be
=IF(ISNA(INDEX(F5:F9,MATCH(B1,D5:D9,0))),"",INDEX(F5:F9,MATCH(B1,D5:D9,0)))

ur detailed description helped me lot