Group text strings from a list in excel

Question

I am trying to make kws grouping using excel, but my formula is becoming gigantic. I was wondering if there is an easier way to do what I am trying to accomplish here.

I have a list like this below

And the expected results are as below

I have tried to use the formula:

=if(or(isnumber(search("query kw",A1,1),(isnumber(search("query 2",A1,1)),"Brand kws","Others"))

But this formula is becoming gigantic, with the number of queries, so it would be great to have a solution, where the search, would search the kws from a list, and I would only need to keep adding more words there instead.

Thanks for the help

Regards Gabriel

Answer 1

One way could be:

Formula in E2:

=CONCAT(IFERROR(IF(SEARCH(" "&A$2:B$4&" "," "&D2&" "),A$1:B$1,""),""))

Note: When in Excel 2019 use Ctrl+Shift+Enter to confirm.

Answer 2

Here is another alternative,

• Formula used in cell E2

=INDEX($A$1:$B$1,SUM(ISNUMBER(SEARCH(" "&$A$2:$B$4&" "," "&$D2&" "))*(COLUMN($A$1:$B$1))))

Since its an array formula, based on your excel version need to press CTRL+SHIFT+ENTER , O365 & Excel 2021 Users don't need to press CTRL+SHIFT+ENTER