CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
William Toscano
William Toscano

Reputation: 225

If cell contains a string listed in range

Trying to output "True" if a cell contains a string listed in a range. See screenshot. This is a simplidied version of an old question I posted here here:

Here is a link to the spreadsheet

enter image description here

Upvotes: 0

Views: 2404

Answers (2)

William Toscano
William Toscano

Reputation: 225

=IF($D3="",,NOT(ISERROR(REGEXEXTRACT(LOWER($D3),JOIN("|",FILTER(LOWER($C$3:$C),$C$3:$C<>""))))))

I found this answer to my question based off of this old question.

enter image description here

Upvotes: 0

Tanaike
Tanaike

Reputation: 201613

I believe your goal is as follows.

  • For example, when a cell "D3" is checked in your showing image, you want to search using the values of all cells "C3:C".

In this case, how about the following sample formula?

Sample formula:

=ARRAYFORMULA(IF(D3:D<>"",REGEXMATCH(D3:D,TEXTJOIN("|",TRUE,C3:C)),""))
  • In this formula, the values of "C3:C" are used as the regex.

Result:

enter image description here

Reference:

Upvotes: 2

Related Questions