0

I have a matrix in my google sheet. I want to color a cell if its value is present in another array, meaning one cell of that array is identical. I am able to do it using conditional formatting and checking only one cell instead of the whole array, but this is not scalable to long arrays.

Here is a practical example:

This is my matrix:

enter image description here

This is the array Z29:Z36

enter image description here

And this is the desired output:

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
tidus95
  • 359
  • 2
  • 14

2 Answers2

2

Or use Countif:

=countif($Z$29:$Z$36,D62)

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
1

use this custom formula on range D62:V:

=REGEXMATCH(D62, TEXTJOIN("|", 1, $Z$29:$Z$36))
player0
  • 124,011
  • 12
  • 67
  • 124