CODEWITHSUNDEEP
excelexcel-formula
adi
adi

Reputation: 207

getting multiple results in a cell using helper column

i am trying to categorize text with keywords. such that column b get category name of text in column A using helper column F and G where F is named range "keywords" and G is named range "category".

enter image description here

i was using this formula from exceljet but this only return first result

=INDEX(category,MATCH(TRUE,ISNUMBER(SEARCH(keywords,a1)),0))

Google is showing use of textjoin formula but I don't have office 365 subscription.

Upvotes: 0

Views: 122

Answers (1)

bosco_yip
bosco_yip

Reputation: 3802

Formula solution.

1] For Office 365 and Excel 2019 user, the formula using Textjoin function

In B1, array formula (Confirm by pressing Ctrl+Shift+Enter) copied down :

=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(F$1:F$5,A1)),G$1:G$5,""))

enter image description here

2] If you don't have Office 365 Textjoin function, try this longer formula solution for all Excel version.

To create a define name >>

  1. Select B1 >> Define Name
  2. Name : ArrayResult
  3. Refer to :

=IFERROR(INDEX($G$1:$G$5,N(IF(1,AGGREGATE(15,6,ROW($A$1:$A$5)/ISNUMBER(SEARCH($F$1:$F$5,A1)),ROW($A$1:$A$10))))),"")

  1. Finish

Then,

In B1 formula copied down :

=SUBSTITUTE(TRIM(IFERROR(INDEX(ArrayResult,1),"")&" "&IFERROR(INDEX(ArrayResult,2),"")&" "&IFERROR(INDEX(ArrayResult,3),"")&" "&IFERROR(INDEX(ArrayResult,4),"")&" "&IFERROR(INDEX(ArrayResult,5),""))," ",", ")

enter image description here

Upvotes: 1

Related Questions