CODEWITHSUNDEEP
excel
JDStopper
JDStopper

Reputation: 3

Excel - Return value based on list of keywords that match substring

Current Setup: I have two excel sheets - one with product information and one with a list of subcategories (each column of the subcategory list has a keyword that is associated with that subcategory).

Desired Result: I would like to return a subcategory for each product based on the keywords in the subcategory sheet. I am currently trying to find a way to find a substring within the product name that contains one of the keywords in the subcategory sheet and then return the relevent subcategory name.

Result Example

Upvotes: 0

Views: 1729

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

Like I said in my comments you will need to expand the lookup array to have one line per word:

enter image description here

Then you can use this formula:

=INDEX($F$1:$F$6,MATCH(TRUE,INDEX((ISNUMBER(SEARCH($G$1:$G$6,A1))),),0))

enter image description here

Edit

As per the photos that just got added:

=INDEX($F$1:$F$3,AGGREGATE(15,6,ROW($G$1:$I$3)/(ISNUMBER(SEARCH($G$1:$I$3,A1))),1))

enter image description here

Upvotes: 2

findwindow
findwindow

Reputation: 3153

Try this in cell F1. Formula needs to be entered by pressing ctrl+shift+enter

=IF(COUNT(SEARCH(C1:E1,A1)),B1,"")

enter image description here

Upvotes: 2

Related Questions