=MATCH with nested arrays assistance needed

[shiner]

I researched this topic at length today, and understand it for the mos
part. I customized it and received a result that was different tha
expected.

The objective of my spreadsheet is to comb through a workshee
containing raw data of completed courses. I have student IDs and cours
names. When I find a match, I want to assign a point value for havin
completed the course.

I have created a sample.xls to demonstrate what I have done and where i
is breaking down.

This equation works well and I receive the desired outcome. In thi
case, there was a singular class name I was looking for in each cell.

Code
-------------------


{=IF(ISERROR(INDEX(Completed_Courses_Raw_Data!$A:$A,MATCH(Calculated_Results!$A4&Calculated_Results!E$2,Completed_Courses_Raw_Data!$A:$A&Completed_Courses_Raw_Data!$B:$B,0))),0,Calculated_Results!E$3)}


-------------------


The twist that I am seeking help with involves when I want to search i
an OR condition for multiple class names. Each year, some classe
change names in the database (awesome right!?!?). So, my objective i
to see if the student has completed one of the acceptable course name
to receive credit for the cell.

When I use this equation, I only return TRUE when the course name in th
first cell is a match - and it appears to ignore the rest of th
optional course names that could also trigger a TRUE.


Code
-------------------


{=IF(ISERROR(INDEX(Completed_Courses_Raw_Data!A:A,MATCH(Calculated_Results!$A4&Courses_w_Multiple_Names!$A$2:$A$20,Completed_Courses_Raw_Data!$A:$A&Completed_Courses_Raw_Data!$B:$B,0))),0,Calculated_Results!$V$3)}


-------------------


The key difference I had here comes in the MATCH definition. Instead o
a singular reference to

Code
-------------------

Calculated_Results!E$2

-------------------


I tried to use a range

Code
-------------------

Courses_w_Multiple_Names!$A$2:$A$20

-------------------


Is there a clean way to accomplish what I was trying to do here

+-------------------------------------------------------------------
|Filename: sample.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=902
+-------------------------------------------------------------------