ISNA with INDEX(MATCH) function not working

Hello,

I'm working on a timetable spreadsheet and having trouble hiding the #NA reports.
I have 2 WS's - Sheet 1 holds the data, and sheet 2 presents a summary of staffs details (as in a school timetable). I have attached 2 pics if they help

The presnt formula works fine, but including the ISNA creates problems!
The formula is:
=INDEX('TT 2012-13'!D2:D335,MATCH(1,('TT 2012-13'!A2:A335=B4)*('TT 2012-13'!B2:B335="Tues")*('TT 2012-13'!C2:C335="AM"),0))

I tried for ages to use VLOOKUP but failed. If there are other solutions I'm happy to adapt

Many thanks

G






data-sheet.jpgsummary-sheet.JPG

Try this

Please Login or Register  to view this content.

You would use ISNA like this:

=IF(ISNA(some_formula),"error_message",some_other_formula)

so, applying this to your situation, you would need something like this:

=IF(ISNA(MATCH(1,('TT 2012-13'!A2:A335=B4)*('TT 2012-13'!B2:B335="Tues")*('TT 2012-13'!C2:C335="AM"),0)),"",INDEX('TT 2012-13'!D2:D335,MATCH(1,('TT 2012-13'!A2:A335=B4)*('TT 2012-13'!B2:B335="Tues")*('TT 2012-13'!C2:C335="AM"),0)))

which will give you blanks instead of #N/A.

Hope this helps.

Pete

Wow, stunningly fast response.
Unfortunately the IFERROR bit generates a #name? error.
Thanks anyhow

G

Originally Posted by kvsrinivasamurthy

Try this

Please Login or Register  to view this content.

IFERROR is not available in XL2003.

Pete

Originally Posted by :) Sixthsense :)

Try this...

Formula:

Please Login or Register  to view this content.

Great response - thankyou, it worked! I'm a very happy chap

Thank you to everybody, for helping me sort this so quickly.

Hi Sixth sense,

If you're still there......
The INDEX(MATCH) lookup is only giving the 1st result it finds that matches the criteria. Can I modify the existing code to continue looking for other matches or do I need to look for another solution. What I really want to happen is to repeat the formula in the next cell below taking into account the matches that have already been made. How can I get the lower cell formula to acknowledge that 1 match has been found already......? A look at the earlier jpegs might help.
Thanks a million
G