Google Sheets - Assign category based on list of search words

Question

I have a list of data, various text. I'd like to assign a category to each row, based on a list of search words.

I've made a mockup document, so you can see what's going on, here.

The mockup example

I have managed to do it, but in a complicated way. This is my current formula: =ARRAYFORMULA(IF(REGEXMATCH(A4:A;F4);E4;IF(REGEXMATCH(A4:A;F5);E5;IF(REGEXMATCH(A4:A;F6);E6;"x"))))

So for each category I have to add another IF sentence.

I have 40+ categories in my original document, which means 40+ IF sentences in the same formula, not ideal.

Then I tried something like this: =ARRAYFORMULA(IF(REGEXMATCH(A4:A;F4:F);E4:E;"x"))

.. which does not work at all.

What to do?

Answer 1

Try this out:

=ARRAYFORMULA(TOCOL(IF(REGEXMATCH(TOROW(A4:A);F4:F6);E4:E6;);1;1))

Edit -- To address the blank values issue, use:

=ARRAYFORMULA(MAP(A4:A;LAMBDA(a;JOIN(;IF(REGEXMATCH(a;F4:F6);E4:E6;)))))