2

What would the syntax be to conditionally format - if any cell in Column A contains a value that matches any value in Column B then display a blue background?

Looks like this would be done using Conditional Formatting under Format, where this custom formula works.

=OR(A1:A1000="Text Sample 1",A1:A1000="Text Sample 2")

Instead of listing a bunch of values in this formula, is there syntax that can represent values listed in another column (Column B)?

player0
  • 124,011
  • 12
  • 67
  • 124
Jason
  • 79
  • 6

2 Answers2

0

use this custom formula:

=REGEXMATCH(A1, TEXTJOIN("|", 1, B$1:B))

0

for exact finds use:

=REGEXMATCH(A1, "^"&TEXTJOIN("$|^", 1, B$1:B)&"$")

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks, This seems to only work for your sample. Change aa in column b to aaa and you'll see what I mean. Its not a true match. – Jason Aug 11 '19 at 19:22
0

Custom Formula: 

=QUERY(B:B," Select count(B) where B is not null and '"&A1&"' contains B label count(B) ''",0)

Apply to:

A1:A
  • Queries Column B for every cell in A starting from A1 and outputs count of cells.
TheMaster
  • 45,448
  • 6
  • 62
  • 85