Conditional formatting cell if value exists on another table with the same column header

Hello –

I’ve been trying to figure out this conditional formatting formula for a couple of days now, so I figure it’s time to reach out for help…

I’m trying to conditionally format an individual cell with a color _if_ the value exists in another table _only_ if the column header matches.
For example:

Input Table (Sheet1) - Contains data under column headers that should be highlighted if they match the same value under the same column heading in the lookup table (Sheet2).


Lookup table (Sheet2) - Values that should not be in the corresponding column on the Input Table (Sheet1). If the value in Lookup Table exists in the equivalent column in the Input table, that value should be highlighted on the Input Table (Sheet1)


Basically, the conditional formatting is checking whether the value appears in the same column on the lookup table, and if so, highlighting the cell.
I hope I’ve explained this clearly enough. I’ve also attached the demo spreadsheet I’ve been working on.



Can anyone assist?

Thank you in advance!

Thank you! However, I wasn't able to get your suggestions to work...

Tables are irrelevant to what I'm trying to do; it all about the column names. I spent several hours working on it, and I got closer to my desired result. It mostly works, with the one issue that changing the column order on either sheet breaks the conditional formatting.

If you'd be kind enough to take a look, I've attached a link to my latest attempt. It works (mostly) as intended, but I need it to work regardless of the column order. I've converted the tables to ranges to eliminate the table aspect.

As you can see, if the column name on either sheet it modified, all of the values turn orange, since there is no matching column name.

I've attached the revised workbook(without tables).

Thanks in advance for helping me out; I've been going insane trying to get this to work!