Conditional Formatting based on similar rows

Hello,

Please note that I have attached the excel file because describing the problem is a bit tough. I think seeing the file may help clarify things.

I am trying to do something that requires somewhat, (at least for me), complex logic using simple functions in Excel. I have 2 tables, Group 1, and Group 2. Each Group has a list of people that are listed out more than once. Each person has an ID, a Number 1 side, and a Number 2 side. I also have a simple one column table called 'Key' that just contains the values I am searching for.

So, what I am trying to do is highlight the entire row of a person who has one of their numbers, either number 1 or number 2, in red, and somebody that has all of their numbers matching with any value in the 'Key' table to be highlighted in green.

I have attached my Excel file to make this easier to follow.

I am doing this on a small scale, and I have had some success using a series of IF statements with embedded VLOOKUPS, and some more success with using IFERROR's with embedded VLOOKUP's, but none of my solutions really work tooo well.

Does anyone have any advice or a solution?

Originally Posted by FlameRetired

Will any of the names have duplicate numbers in columns D:E?

Hi Flame. No there should not be any duplicates across rows. So, on one line a person won’t have a number in both ‘Number 1’ column and in ‘Number 2’ column. The numbers between Number 1 and Number 2 should also be different for each person. However, if a person has multiple ‘Number 1’ columns, then it is possible there will be a duplicate value. But a duplicate will never happen across columns D:E