Conditional Formatting of Cells based on variable input in other cells.

[ScottMN]

I would like to highlight / conditionally format the cells containing "data" Row 23 (item 10 data b23:O23) which have numbers that match those "variables" placed in Row 12 (item 10 variables b12:k12). See screenshot attached.  The rows are necessarily different sizes (# of cells), and each row of "variables" may contain different numbers of populated cells

I've dumbed this way down, as I just want to get this working then can apply more globally.  Intent is that I will be doing this for the remainder of the rows as well (i.e. item 1 selections with item 1), so ability to replicate easily would be appreciated.

I've tried a number of different intuitive options (=or) and also done some searching and saw a =countif option (which did not work...but didn't understand it to be honest).

Not sure why this is so complex.... 

[#2 · Accepted solution]

@ScottMN Perhaps something like in the attached file?

[#3]

@Riny_van_Eekelen- smashed it!  That is awesome.  I wish I understood the logic in the back a bit more to make it more intuitive (and know that some of these other cool functions exist) - but Ill take it.

Appreciate the prompt response mate - really helps me out.   

[#4]

@ScottMN Conditional Formatting (CF), unfortunately, isn't always intuitive. Think of it this way. The range you want to format is B14:O23. That will thus be in the the "Applied to" field with dollar signs to make the references absolute. Now, the rule that checks if the numbers in the top part of the sheet exist in the bottom part uses MATCH. If the number exists it produces a number (representing the position where the number was found) or #N/A.

Then ISNUMBER(.......) will return ether TRUE or FALSE. Put it all in one CF rule like this:

=ISNUMBER(MATCH(B14,$B3:$O3,0))

Applied to the range mentioned above will lead to all cells with TRUE to be formatted.

The attached file includes a few extra rows to demonstrate just that with the MATCH part of the above formula entered in B25, copied across and down and the ISNUMBER formula in the rows below that.

Hope you'll find it useful.