Google Sheets Conditional Formatting if cell value contains value in range

1
votes

I saw an example a few days ago about using SUM but I cannot find it now... What I want to do:

Sheet 1 Column A holds comma separated lists example:

  • A1 -> dog, cat, goat
  • A2 -> pig, sheep, monkey

Sheet 2 Column A holds individual list items per row example:

  • A1 -> Pig
  • A2 -> Cow
  • A3 -> Goat

I want Sheet 1 to highlight cell if it finds a value from range in Sheet2

I have tried text contains Sheet2!A:A and a couple of custom formulas such as SEARCH(Sheet2!A:A, Sheet1!A:A) (and FIND) and even FIND(Sheet1!A1, "pig" OR "cat") <- this works for one value not AND/OR not sure if I need to use arrayformula because I guess I dont really know what arrayformula is doing to what values.

I suppose I could write a search loop script but I thought I could get this done fairly easy with conditional formatting.

1
arrayslistgoogle-sheetsgs-conditional-formatting

1 Answers

0
votes

Try this conditional formatting custom formula:

=ArrayFormula(SUM(LEN(A1)*REGEXMATCH(A1,"(?i)(^|, )"&INDIRECT("Sheet2!A:A")&"($|, )")))

applied to range A:A. Note: this will not require matches to be case-sensitive.