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

2

Best Answer

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:

  • TEXTJOIN
  • REGEXMATCH
=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

Random Posts

Github "Updates were rejected because the remote contains work that you do not have locally."Matlab - multiply matrix with vector of matricesWhat is the best way to clean up an Object in Java?Delete from two tables in one queryHow can one play RTSP videos on browser with lowest possible latency?if-else statement inside jsx: ReactJSIRS E-File API?What are Kafka transactions?Breaking out of a loop from within a function called in that loopHAProxy 2.5 http-request replace-path not working correctlyCrystal Reports for VS2019APL versus A versus J versus K?Pandas groupby cumulative sumSplunk - Use streamstats with transaction command to find gaps in event logsUnderstanding JAX argnums parameter in its gradient functionWarning: comparison with string literals results in unspecified behaviourA long bigger than Long.MAX_VALUEIf cell contains a string listed in rangeAsdf wont switch local version based on tool-versions or using the local commandI want to replace single quotes with double quotes in a list