Find a value and check it matches and compare

Greetings

I am working on a little project

I need help with structuring a formula that will look a value in D4 and compare it to a value in H4

We need to look up the value from D4 in the table in column O and see in line between P to T if the second value can be found. If it is found, need to shaded the cell h4 Green if not shade the cell h4 Red.

We then do the same for D5, D6 and D7

The value in D and H can change each time we run the utility


I thank you for all the help

Kind regards

Vraj

Hi

So sorry I did not make myself clear and realise that I caused a confusion

O2 to s 11 will be the lookup table

It should I think easier to read if I had stated that we look at D4 and check in the table in O if it matches the value in H5. if it does then we should shade K4 as Green if no match then shade K4 red.

Likewise for D5 check if the value has a match in the table O and if the corresponding columns contain the value that matches H5 and then K5 will be shaded Green if not Red

we do the same for d6 and D7

I trust that may be what I was initially looking for

The value in D4 and H5 can change

Kind regards

Vraj

See if this will help.
1) Paste the following formula into cell K4 and drag down to K7: =SUMPRODUCT((O$3:O$11=D4)*(P$3:S$11=H$5))
2) Select cells K4:K7
3) Select conditional formatting and use the following as rules
For Red (font and fill): =K4=0
For Green(font and fill): =K4=1
Let us know if you have any questions.