Search a column for cell contents, in any order

[cash2021]

Hi! I have a basic formula that searches a column for a cell's contents - in the attached example, I'm checking if the text in B2 ('affordable plumbers') appears in any of the cells in column A. I'd like to evolve this formula (or replace it entirely) with a formula that can check if the contents of cell B2 appear *in any order* in any cell in Column A. In this example, I'd want to also yield a 'yes' next to the targeted keyword 'plumbers that are affordable', because it contains both 'plumbers' & 'affordable'. Formula I'm using is below! TIA!

=IF(COUNTIF($A$2:$A$1048576,"*"&B2&"*"),"Yes","No")

 

[DanteAmor]

I'm checking if the text in B2 ('affordable plumbers') appears in any of the cells in column A.

Are you only going to search for 2 words or can it be more than 2 words?

 

[cash2021]

It can be more than 2 words! Honestly though, it likely wouldn't be impactful past 4 words (low volume).

 

[DanteAmor]

Try:

=IF(ISNUMBER(SEARCH("1",TEXTJOIN("",,LET(a,B2,BYROW(ISNUMBER(SEARCH(TEXTSPLIT(a," "),$A$2:$A$100))*1,LAMBDA(br,IF(SUM(br)=LEN(a)-LEN(SUBSTITUTE(a," ",""))+1,1,0))))))),"Yes","No")