Index Match with multiple criteria involving find text

Struggling with Finding an index match formula that will ultimately be used between two workbooks. I've attached a sample file greatly amended.

looking for a formula in column B on the cancellation worksheet that will enter the word "Recovered" if the New Trip ID matches a Trip ID on the complaints worksheet Column B and the corresponding Previous Activity Comments Column C contains the phrase "Trip Recovered." or leave the cell blank.
This phrase may be in the beginning, middle or end of the comments (so looking for perhaps a wildcard here)

Thanks for any help.

I highlighted the comments that contain the phrase and also added a column on the Cancellation worksheet that show which cells to the left should contain the "Recovered" text

Hello
Try the following formula in B2 down:

Formula:

Please Login or Register  to view this content.

Hope this helps.
DBY

Hi DBY
This work well.
I'd like to see if there is a way to do it with INDEX Match since they may change the column format so comments is left of the trip ID. If I don't see another response by mid afternoon then I'll close with my sincere thanks for a great formula.

Thank you for the INDEX Match formula
Really appreciate your time and response.

Glad it's solved. Thanks for the Rep mark. Thanks Glenn for helping out.

DBY