Return a category lookup formula

[tigerzen]

Looking for a formula to populate the category column automatically so basically if the description column contains one of a series of words then a particular country is returned. The attached example will indicate what I'm trying to achieve with the desired solution just below the actual question.

Book6
	A	B	C	D	E	F
2	Item	Category		Europe	US	Asia
3	Paris France			France	Texas	China
4	Belgium waffles			Belgium	New York	Thailand
5	Texas hotcakes			Austria		Japan
6	Statues in Austria			Italy
7	soup in China
8	Japan sword
9
10	Item	Category
11	Paris France	Europe
12	Belgium waffles	Europe
13	Texas hotcakes	US
14	Statues in Austria	Europe
15	soup in China	Asia
16	Japan sword	Asia
Sheet1

 

[awoohaw]

First I would suggest you make your lookup a flat lookup with two columns one for the country and one for the continent.
but, if that is impossible, you can use this. It is very complicated:

Book1
	A	B	C	D	E	F
1	Item	Category		Europe	US	Asia
2	Paris France	Europe		France	Texas	China
3	Belgium waffles	Europe		Belgium	New York	Thailand
4	Texas hotcakes	US		Austria		Japan
5	Statues in Austria	Europe		Italy
6	soup in China	Asia
7	Japan sword	Asia
8
9	Item	Category
10	Paris France	Europe
11	Belgium waffles	Europe
12	Texas hotcakes	US
13	Statues in Austria	Europe
14	soup in China	Asia
15	Japan sword	Asia
16
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=INDEX(TAKE(TEXTSPLIT(TEXTJOIN(",",TRUE,$E$2:$E$3&";"&$E$1) & "," & TEXTJOIN(",",TRUE,$F$2:$F$4&";"&$F$1) & "," & TEXTJOIN(",",TRUE,$D$2:$D$5&";"&$D$1),";",","),,-1),MATCH(INDEX(TEXTSPLIT(A2," "),SUM((--(ISNUMBER(MATCH(TEXTSPLIT(A2," "),TAKE(TEXTSPLIT(TEXTJOIN(",",TRUE,$E$2:$E$3&";"&$E$1) & "," & TEXTJOIN(",",TRUE,$F$2:$F$4&";"&$F$1) & "," & TEXTJOIN(",",TRUE,$D$2:$D$5&";"&$D$1),";",","),,1),0))))*SEQUENCE(,COUNTA(TEXTSPLIT(A2," ")),1,1))),TAKE(TEXTSPLIT(TEXTJOIN(",",TRUE,$E$2:$E$3&";"&$E$1) & "," & TEXTJOIN(",",TRUE,$F$2:$F$4&";"&$F$1) & "," & TEXTJOIN(",",TRUE,$D$2:$D$5&";"&$D$1),";",","),,1),0))

 

[awoohaw]

Here is a miniworkbook with both approaches:

Book1
	A	B	C	D	E	F	G	H	I
1	Item	Category	Better	Europe	US	Asia		France	Europe
2	Paris France	Europe	Europe	France	Texas	China		Belgium	Europe
3	Belgium waffles	Europe	Europe	Belgium	New York	Thailand		Austria	Europe
4	Texas hotcakes	US	US	Austria		Japan		Italy	Europe
5	Statues in Austria	Europe	Europe	Italy				Texas	US
6	soup in China	Asia	Asia					New York	US
7	Japan sword	Asia	Asia					China	Asia
8								Thailand	Asia
9	Item	Category						Japan	Asia
10	Paris France	Europe
11	Belgium waffles	Europe
12	Texas hotcakes	US
13	Statues in Austria	Europe
14	soup in China	Asia
15	Japan sword	Asia
16
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=INDEX(TAKE(TEXTSPLIT(TEXTJOIN(",",TRUE,$E$2:$E$3&";"&$E$1) & "," & TEXTJOIN(",",TRUE,$F$2:$F$4&";"&$F$1) & "," & TEXTJOIN(",",TRUE,$D$2:$D$5&";"&$D$1),";",","),,-1),MATCH(INDEX(TEXTSPLIT(A2," "),SUM((--(ISNUMBER(MATCH(TEXTSPLIT(A2," "),TAKE(TEXTSPLIT(TEXTJOIN(",",TRUE,$E$2:$E$3&";"&$E$1) & "," & TEXTJOIN(",",TRUE,$F$2:$F$4&";"&$F$1) & "," & TEXTJOIN(",",TRUE,$D$2:$D$5&";"&$D$1),";",","),,1),0))))*SEQUENCE(,COUNTA(TEXTSPLIT(A2," ")),1,1))),TAKE(TEXTSPLIT(TEXTJOIN(",",TRUE,$E$2:$E$3&";"&$E$1) & "," & TEXTJOIN(",",TRUE,$F$2:$F$4&";"&$F$1) & "," & TEXTJOIN(",",TRUE,$D$2:$D$5&";"&$D$1),";",","),,1),0))
C2:C7	C2	=INDEX($I$1:$I$9,SUM(IFERROR(MATCH(TEXTSPLIT(A2," "),$H$1:$H$9,0),0)),0)

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F
1	Item	Category		Europe	US	Asia
2	Paris France	Europe		France	Texas	China
3	Belgium waffles	Europe		Belgium	New York	Thailand
4	Texas hotcakes	US		Austria		Japan
5	Statues in Austria	Europe		Italy
6	soup in China	Asia
7	Japan sword	Asia
8
Main

Cell Formulas
Range	Formula
B2:B7	B2	=TOCOL(VLOOKUP(TEXTSPLIT(A2," "),HSTACK(TOCOL($D$2:$F$7,1),TOCOL(IF($D$2:$F$7<>"",$D$1:$F$1,1/0),2)),2,0),2)

 

[tigerzen]

Thanks guys a 2 columnar approach is perfectly fine, I'd like to avoid any unnecessary complication wherever possible.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[shinigamilight]

=INDEX($D$1:$F$1,,AGGREGATE(14,6,IF(SEARCH(IF($D$2:$F$5<>"",$D$2:$F$5),A2)>0,COLUMN($D$1:$F$1))-3,1))