Match partial word: vlookup or index match?

[Vcoppens]

Hi,

I have table1 with in the description in column A a range of words.

The range of words is every time different but some of those words have a common word such as apples, pears, orange, duck or geese.

Table2 has the words that if they appear in the range of words in table1 should result either in X, Z or O.

I am looking for the following solution:
In the first table all rows that have the words "apples" or "pears" or "orange" in column B with an X
All rows with the words "duck" or "geese" in column B with a Z
and any row that doesn't contain any of those words with a O

I have been trying to solve this with vlookup with a partial look up of the word "*"&D2&"*" and index and match but I am not getting the desired results.

Would someone have a suggestion?

Thank you!

Description	X/Z/O		If Description contains	Then show as
Big brown apples	x		apples	x
Small red apples	x		pears	x
Not so good apples	x		orange	x
Anjou pear too small	x		duck	z
Green pears from France	x		geese	z
Orange from Spain	x		remaining category	o
Great Nothern Duck	z
White Duck	z
Flamboyant Duck	z
Canadian geese this week	z
Geese not to expect to come back	z
Green house	o
Dead end Street	o
Not feeling well	o

 

[DrandonC]

=VLOOKUP("*apples*",A:B,2,0)

 

[jtakw]

Hi,

A couple things,
1. In my sample, D2:D6, change the items in your Table, to All Singular (e.g. apples to apple, pears to pear, etc.)
2. This formula will still work for overlapping words like "pineapple" in my sample A14, and "appears" in A15.

Book3.xlsx
	A	B	C	D	E
1	Description	X/Z/O		If Description contains	Then show as
2	Big brown apples	x		apple	x
3	Small red apples	x		pear	x
4	Not so good apples	x		orange	x
5	Anjou pear too small	x		duck	z
6	Green pears from France	x		geese	z
7	Orange from Spain	x		remaining category	o
8	Great Nothern Duck	z
9	White Duck	z
10	Flamboyant Duck	z
11	Canadian geese this week	z
12	Geese not to expect to come back	z
13	Green house	o
14	Dead end Street pineapple	o
15	Not feeling appears well	o
Sheet1063

Cell Formulas
Range	Formula
B2:B15	B2	=IFERROR(LOOKUP(2,1/SEARCH(" "&D$2:D$6," "&A2),E$2:E$6),"o")

 

[Vcoppens]

Hi Jtakw,

Thank you so much for your quick reply.

I will try it out and let you know if it works.

Kind regards,

 

[Vcoppens]

Hi Jtakw,
Thank you so much! Your formula worked like a charm.
Kind regards,

 

[jtakw]

You're welcome, thanks for the feedback.