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
CodePudding user response:
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.
CodePudding user response:
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