Search code examples
google-sheetsgs-conditional-formatting

How can you apply conditional formatting to a two dimensional set of values, based on matching with another two dimensional set in google sheets?

I've looked for answer to this, and the reason why I can't find it is probably because I don't know exactly how to word the question, but here it goes:

Say I have a table in the google sheet Table 1

I want the values in this table to be formatted based on values in another table similar to this Table 2

So in this example Yellow, Green, Black, White, and Orange would be highlighted in the first table based on the inputs in the second. Their positions don't matter, I just want it to find the exact same value and highlight it.

For the life of me I can't figure this out, and again I have no idea how to word the question in a search engine for it to tell me. Could someone help me out here?

Solution

  • Just count if the value of the cell matches at least 1 of the values of your criterias. Use mixed references.

    I made this dataset:

    enter image description here

    The highlighted values are highlighted because their value appear at least once in their respective column:

    I've created a conditional formatting rule based on this formula:

    =COUNTIF(B$11:B$14;B2)>0

    Note I've used mixed references (blocked only the row numbers in the criteria range).

    I've applied like this:

    enter image description here

    Hope this helps.

    UPDATE: OP just want to highlight the values in common between both datasets, regardless their position, so we can use almost the same conditional formatting rule, but we need to adapt the formula like this:

    =COUNTIF($B$11:$F$14;B2)>0

    You want to count always in the same criteria range, so we use absolute references to range B11:F14

    Now, it works like this:

    enter image description here

    Notice the values Silver,Orange are highlighted too, even if they appear in a different column on the second table.