Incorrect Grade results using VLOOKUP

[cobbrjr]

I want to evaluate a cell entry in the COG and match it with the content of the table in the Program of Study. The result is the GRADE that is in the 4th column in the corresponding row (in the Program of Study). The grade is be be placed beside the corresponding course in the COG.

It seems to work when the cell entry being evaluated matches an entry in the first column of the Program of Study. It does not result in the correct GRADE consistently when the entry in the cell in COG is being compared with a "course substitution" (sub 1 or sub 2) in the 5th column in the Program of Study.

I attempted to use the VLOOKUP function but it seems not to give accurate results consistently. See the COG Before and COG After tabs to observe the desired results.

[JohnTopley]

Rather more complex lookup to find results in possibly 1 of 3 columns:

=INDEX('Program of Study'!$D$13:$D$100,IFERROR(MATCH($G44,'Program of Study'!$A$13:$A$100,0),IFERROR(MATCH($G44,'Program of Study'!$E$13:$E$100,0),MATCH($G44,'Program of Study'!$F$13:$F$100,0))))


the above is for LIBS202

Highlighted cells need to be changed for each cell containing subject

see "Cog(Before)"