Picking up Data with Lookup Formula

Hi all,

I'm trying to pick up data and reflect it in a gantt chart (using conditional formatting).
Please refer to my attached file for better understanding.

I have tried to use lookup formula (following the conditional formatting formula) to reflect the Ship Name in the gantt chart based on the raw data on the left tables.
However, if I input the same range date for different Equipment name, the formula will always pick up the last Ship name into the gantt chart.

Example:
EY 01 = 1 July - 3 July, Ship ABC --> it picks up DEF instead of ABC in the gantt chart on the right
EY 02 = 1 July - 3 July, Ship DEF

Appreciate your help, thanks!

Hi Special-K!

Thank you for your help. It works wonders! Appreciate it!