ExcelBanter - =MATCH with nested arrays assistance needed

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

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

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

This equation works well and I receive the desired outcome. In this 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 in an OR condition for multiple class names. Each year, some classes change names in the database (awesome right!?!?). So, my objective is to see if the student has completed one of the acceptable course names to receive credit for the cell.

When I use this equation, I only return TRUE when the course name in the first cell is a match - and it appears to ignore the rest of the 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 of 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?