If...
search range: A1:A10
search key: B1:B3
... then use the following formula
=arrayformula(sum(if(regexmatch(textjoin(",",false,",",A1:A10,","),","&B1:B3&","),1,0)))>0
Feel free to read the documentation of the functions in question.
The basic idea here is that: we want to be able to join the search words into 1 string and apply arrayformula to individual search keys; and then, we want to search whole words.
So how do we easily search whole words? Your search words are divided by cells. So lets put , between them but also wrapping them. Now ","&search_key&"," marks a matched word -- not just a component of a search word.
The rest is doing and operation on array. Google Sheet unfortunately doesn't have functions like any or all. So the most (computationally) efficient thing to do is to use if (in comparison to alternatives like matrix multiplication or filter). The position of arrayformula doesn't matter here so you can just put it outside everything.
I am looking for a way to find if a text is in a range of cells but the cell does not have to be the exact match it just has to contain the text anywhere within the cell. Something like the search function but for a range of data. Using this to compare one list to another and then generate another list that tells me everything that is the same.
If...
search range: A1:A10
search key: B1:B3
... then use the following formula
=arrayformula(sum(if(regexmatch(textjoin(",",false,",",A1:A10,","),","&B1:B3&","),1,0)))>0
Feel free to read the documentation of the functions in question.
The basic idea here is that: we want to be able to join the search words into 1 string and apply arrayformula to individual search keys; and then, we want to search whole words.
So how do we easily search whole words? Your search words are divided by cells. So lets put , between them but also wrapping them. Now ","&search_key&"," marks a matched word -- not just a component of a search word.
The rest is doing and operation on array. Google Sheet unfortunately doesn't have functions like any or all. So the most (computationally) efficient thing to do is to use if (in comparison to alternatives like matrix multiplication or filter). The position of arrayformula doesn't matter here so you can just put it outside everything.
Here's another possible solution:
=ARRAYFORMULA(IF(BYROW(A5:C,LAMBDA(r,SUM(LEN(r))))=0,,BYROW(REGEXMATCH(A5:C,"\b"&TEXTJOIN("\b|\b",1,E1:E)&"\b"),LAMBDA(r,SUM(--r)>0))))
Note that this formula is entered once in D5 and it doesn't have to be dragged down.
Check If A Range Contains A String - Google Docs Editors Community
If cells in range contain specific text, return cell in different range? - Google Docs Editors Community
google sheets - If cell contains a string listed in range - Stack Overflow
Google Sheets formula for “if contains” from range - Web Applications Stack Exchange
Does Coefficient work with both Google Sheets and Excel?
What is Coefficient's AI Sheets Assistant?
What is Coefficient?
You could use COUNTIF.
For example:
=COUNTIF(G:G,6) > 0
if you want to find the number 6 in the range G:G, and/or
=COUNTIF(A1:B7,"d") > 0
if you want to find d in the first seven rows of the first two columns.
COUNTIF will return the number of instances in its given range that are equal to its given value. It can also do a bit more complex conditioning (e.g all values higher than 6, or containing the letter A); see the docs for more info.
You could try MATCH:
A1 = [Value you are trying to find]
B1:B10 = [Range you are searching in]
=IF(ISERROR(MATCH(A1;B1:B10;0));"Not found";"Found")
You can replace the A1 with formulas that return a value to search for. The 0 ensures actual search, not presupposing an ordered search range.
Ref: Documentation for MATCH
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:
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.
for "true / false" part:
=IF(ISTEXT(REGEXEXTRACT(A1;"sites"));"True";"False")
for "more words crossreferenced with another sheet" part:
=IF(ISTEXT(FILTER({'sheet1'!A1:A3};{'sheet1'!A1:A3}=A1));"True";"False")
sadly, this is the answer for a question, that wasn't asked in the initial post:
Not really what I wanted, but this {'sheet1'!A1:A} helped. So now I'll try to continue myself and if I'll stuck I'll ask again – Lukasz Kawalec 21 hours ago
...
So it doesn't work :( I've prepared Google Spreadsheet, so maybe it will be more clear what I try to achive. Here is the link: https://docs.google.com/spreadsheets/d/1ibLoxybv6jZi9pMJJOo5R7LCT1JgnJd6JRzEDnslUxs/edit?usp=sharing – Lukasz Kawalec 5 hours ago
SPREADSHEET: https://docs.google.com/spreadsheets/d/
GIF: https://i.sstatic.net/iJOxV.gif (open in new window)
Hey everybody,
I'm having trouble figuring out a conditional formatting function.
I have a cell, that I would like to change color based on the text of several other cells...
I'm using this sheet to keep status on several parts of a project and want the "header" of each section to update if all sub-sections are marked as complete.
So, 3 things I want to happen...
I want cell A5 background to turn green IF all cells in a certain range contain the text "A" (or are blank)
If any of those cells contain any text other than "A" I want them to turn yellow
If any of the text contains the text "M" I want cell A5 to be RED.
not sure if this makes sense... I can't share the sheet because it is semi-confidential, but I might be able to make a duplicate, with different info if this doesn't make sense.
This is probably a trivial question, but how do I produce the behavior given in the image below?
So when a cell contains the string "abc", it searches the Items column for cells containing this substring and populates with the string "abc|def|ghi", and if a cell contains a missing substring like "abe", then it populates with N/A.