Additional variables for LOOKUP formulas

I need help assigning specific text to a class depending on the name of the class and/or the due date. Here is the current formula I’m using thanks to another user (daffodil11)….

=IFERROR(
IF(YEAR(G2)=YEAR(TODAY())-1, YEAR(G2),
LOOKUP(2,1/(
('Quarterly Classes'!$G$7:$G$28='Training Detail Report'!D2)*
(MONTH('Quarterly Classes'!$I$7:$I$28)&DAY('Quarterly Classes'!$I$7:$I$28)=MONTH('Training Detail Report'!G2)&DAY('Training Detail Report'!G2))),
'Quarterly Classes'!$E$7:$E$28)),
"No Matching Record")


And here’s what I want to add bc of the errors I’m getting with the current formula (formula is great, it just needs some extra variables added)…

1st Variable
If it either column F or G (on the first tab) have 2013 in them, I want column R (on the first tab) to show 2013…. In other words, I want it to look at both the assigned date and the due date to see if either contain 2013.

2nd Variable
The first formula looks at the date due as well as the name of the course in order to figure out what quarter to fill in on column R (on the 1st tab). BUT for a handful of classes (G:2-G:6 on the 2nd tab) there is every due date under the sun, so those specific classes need to populate the quarter by looking only at the name of the class and not the date.

For instance, if the class in column D (on the 1st tab) says “Operations New Hire Orientation”, I want column R (on the 1st tab) to populate with “New Hire Requirement” (matching E3 on the 2nd tab).

Any help you can provide would be greatly appreciated!

Is this solved?

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

Thread closed.