CODEWITHSUNDEEP
excelexcel-formula
SoftTimur
SoftTimur

Reputation: 5532

Assign a category to a text

I have the following Excel table:

enter image description here

Column B and Column D are given data. I'm looking for formulas for Column E. The logic is that, given a text, we search categories appearing in it: if we find one category, we return it; otherwise, we return an empty string. It is not possible to have 2 categories appearing in one text.

Does anyone know how to write these formulas? That could be single formulas, CSE array formulas or dynamic array formulas.

Upvotes: 0

Views: 171

Answers (2)

SoftTimur
SoftTimur

Reputation: 5532

I have found a solution: {=INDEX($B$4:$B$6;MATCH(1;--ISNUMBER(SEARCH($B$4:$B$6;D4));0))} (CSE array formulas) in cell E4, then copy-paste down.

Upvotes: 0

P.b
P.b

Reputation: 11588

In cell E4 use: =IFERROR(INDEX($B$4:$B$6,MATCH(1,(COUNTIF($D4,"*"&$B$4:$B$6&"*")),0)),"")

Enter with ctrl + shift + enter and drag down.

Upvotes: 3

Related Questions