CODEWITHSUNDEEP
validationgoogle-sheetsgoogle-sheets-apispreadsheet
Tane
Tane

Reputation: 491

Check if a range contains text from the another range

Both the range A1:A3 and B1:B3 contain some text. I would like to have the range B1:B3 have a function that checks if a text in one of its cells can be also found in the range A1:A3. In this case, that specific cell should then have lets say, background-color green. How could I achieve this in Google Sheets? In the example image cell B3 contains the text that can be also found on cell A2, and that's why the background-color is green. That's what I would like to happen, automatically.

enter image description here

Upvotes: 0

Views: 442

Answers (1)

JPV
JPV

Reputation: 27262

In conditional formatting, use as a custom forumula

=len($B1)*countif(A:A; "*"&$B1&"*")

and see if that works?

Upvotes: 1

Related Questions