Using an array and conditional formatting

Hi all,

I'm new to the forum, so "hello".

I have a challenge, which i have attached a spreadsheet for advice. It's probably easier if you look at the spreadsheet, rather than me trying to explain. I've added some notes for reference.

i've tried to use an IF/vlookup function but i cant seem to make it work.

Any ideas or support is greatly appreciated.

thanks,
Sean