Max If OR Statement Help

Question

Column B: Name

Column D: Course

Column E: Frequency

Column F: Training Date

Column G: Expiration Date

Column H: Retraining Date

Column I: Status

Here are my formulas:

1. = MAX(($B$13:$B$318=B13) * ($D$13:$D$318=D13) * ($E$13:$E$13="One Time") * ($F$13:$F$318))
2. = MAX(($B$13:$B$318=B13) * ($D$13:$D$318=D13) * ($H$13:$H$318))

I would like to modify the above formulas into one If or statement to include the following:

1. If the frequency is One Time then the cell is to use the formula in 1)
2. if the frequency is not One Time then the cell is to use the formula in 2)
3. If there is no Retraining Date then "Not Found"
4. The date range is 01/01/2024 through 12/31/2024 - this formula should update when the year changes.

Thank you for the help.

Answer 1

=IF(H13="","Not found",
IF(E13="One Time",
MAX(($B$13:$B$318=B13)*($D$13:$D$318=D13)*($E$13:$E$13="One Time")*($F$13:$F$318)),
MAX(($B$13:$B$318=B13)*($D$13:$D$318=D13)*($H$13:$H$318))))

If you need further help I need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.