Finding completion date from a waterfall production schedule

Hello all,

I'm working with a production schedule where we're producing cars of different colors in a repeating sequence. I have a planned schedule, and a recovery schedule for when the plan is disrupted. Data is listed in Row 2 & Row 3 for Red Car #1. As more and more car builds are added to the schedule, I want to be able to pull the completion dates and place them into my "Completion Dates" tab in column C. I want the formulas for column C to look for the combination of text from column A & B and find them in the "Schedule" tab, and return the date in Row 1 that is listed for that particular build's completion (highlighted orange). If there is text in the "Recovery" row, I want that to show, but if not, I'd like it to default to the "Plan" row. I've tried using index, lookup, vlookup, reference, etc. and I can't seem to get anything to do exactly what I'm looking for. I provided a few examples on the "Completion Dates" tab to show what values should be showing if the formulas work correctly.

Let me know if you can help
Thanks!

Car production schedule.xlsx

My proposal.

Formula:

Please Login or Register  to view this content.

Thank you for your reply, those formulas don't seem to work for my version of excel, the second I change anything on the "schedule" sheet, I get a #NAME? error for all the cells with your formula in Column D.

Try in C2 and copy down:

Formula:

Please Login or Register  to view this content.

Good luck!