Vlookup on phrase in large block of text

[eaxlns]

I am trying to do a vlookup on a phrase from a block of text to return a value.

For example, on Sheet2, in column A I have

sensory toys

Then on Sheet1 in column A in one cell it says

Mighty Toddler Busy Board for 2 Year Old plus. Sensory Board Buckle Toy with Extra Toddler Learning Activities. Fidget Board, Toddler Travel Toys, Autism Sensory Toys, Busy Boards for Montessori.

all in the same cell then in column B it says LISTING TITLE

I need a formula that would look for the data in Sheet1, in this case sensory toys through all of the data in column A on Sheet1 and then return the value in column B when it finds it.

Secondly, how could I take this formula one step further and return multiple values if sensory toys appeared in more than one cell?

Thanks in advance.

FINDING KEYWORDS.xlsx
	A	B
1	Mighty Toddler Busy Board for 2 Year Old plus. Sensory Board Buckle Toy with Extra Toddler Learning Activities. Fidget Board, Toddler Travel Toys, Autism Sensory Toys, Busy Boards for Montessori.	LISTING TITLE
2	The Toddler Activity Board, With More! While other sensory learning toys stop at belts, laces, snaps and buckles, we kept on going with a sensory play toy that teaches time and date, and fun activities not for any other purpose than as toddler fidget toys! Practice, learn, and play with Mighty Toddler!	BULLETS
3	Skill Board Features: This double-sided, 14x10.5” busy book helps improve 7 basic life skills (button a coat, open and close a snap, zip, buckles and Velcro, latch a bag and tie a lace), have fun (apple game and faces game), and learn with pride (day, month, date, time, season, weather activity).	BULLETS
4	Unique To This Velcro Board: To use zippers, kids need to learn about that annoying little latch! So while our zipper is attached to the board, it separates completely, so kids can learn it. Laces are affixed, to prevent them getting lost, and we included a pocket to store pieces or other things!	BULLETS
5	Is It Easy, Or Hard? Everything is hard the first time, that’s why us parents always say “practice makes perfect” Now imagine the beaming pride on your toddlers face when they turn that hard, into success! Flexible, soft and lightweight with handles, kids can carry it everywhere to maximize practice.	BULLETS
6	For Toddlers, and Kids Ready for Shoe Tying Practice! There are small pieces, so use this busy board for 1 year old or 1-3 years with adult supervision. For 3-4 years or more, use sensible parental discretion. Available in pink or blue sensory boards, choose their favorite color and try it now!	BULLETS
Sheet1

FINDING KEYWORDS.xlsx
	A
1	PHRASE
2	sensory toys
3	sensory toys for toddlers 1-3
4	learning toys for toddlers 1-3
5	busy board
6	autism toys
7	autism sensory toys
8	learning toys
9	toddler learning toys
10	busy board for 1 year old
11	toddler learning
Sheet2

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[eaxlns]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for the heads up. I have updated this now.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C
1	PHRASE
2	sensory toys	LISTING TITLE	LISTING TITLE
3	sensory toys for toddlers 1-3	#N/A
4	learning toys for toddlers 1-3	#N/A
5	busy board	LISTING TITLE	LISTING TITLE, BULLETS 5
6	autism toys	#N/A
7	autism sensory toys	LISTING TITLE	LISTING TITLE
8	learning toys	BULLETS 1	BULLETS 1
9	toddler learning toys	#N/A
10	busy board for 1 year old	BULLETS 5	BULLETS 5
11	toddler learning	LISTING TITLE	LISTING TITLE
12
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=INDEX(Sheet1!$B$1:$B$6,XMATCH("*"&A2&"*", Sheet1!$A$1:$A$6,2))
C2:C11	C2	=TEXTJOIN(", ",,FILTER(Sheet1!$B$1:$B$6,ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$6)),""))

 

[eaxlns]

You, sir, are a genius! Thank you!

Taking this one step further would it be possible to highlight the resultant cell by colour, depending on the result? For example

LISTING TITLE = Yellow Font
BULLETS 1 = Red Font

 

[Fluff]

Like

+Fluff 1.xlsm
	A	B
1	PHRASE
2	sensory toys	LISTING TITLE
3	sensory toys for toddlers 1-3	#N/A
4	learning toys for toddlers 1-3	#N/A
5	busy board	LISTING TITLE
6	autism toys	#N/A
7	autism sensory toys	LISTING TITLE
8	learning toys	BULLETS 1
9	toddler learning toys	#N/A
10	busy board for 1 year old	BULLETS 5
11	toddler learning	LISTING TITLE
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=INDEX(Sheet1!$B$1:$B$6,XMATCH("*"&A2&"*", Sheet1!$A$1:$A$6,2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B11	Expression	=$B2="Bullets 1"	text	NO
A2:B11	Expression	=$B2="Listing title"	text	NO

 

[eaxlns]

Thank you, how can this be done for Column C? Would there be a clash if there are multiple results?

 

[Fluff]

Would there be a clash if there are multiple results?

Yes. Only one rule would work

 

[Fluff]

You would have to arrange the rules to suit

+Fluff 1.xlsm
	A	B	C
1	PHRASE
2	sensory toys	LISTING TITLE	LISTING TITLE
3	sensory toys for toddlers 1-3	#N/A
4	learning toys for toddlers 1-3	#N/A
5	busy board	LISTING TITLE	LISTING TITLE, BULLETS 5
6	autism toys	#N/A
7	autism sensory toys	LISTING TITLE	LISTING TITLE
8	learning toys	BULLETS 1	BULLETS 1
9	toddler learning toys	#N/A
10	busy board for 1 year old	BULLETS 5	BULLETS 5
11	toddler learning	LISTING TITLE	LISTING TITLE
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=INDEX(Sheet1!$B$1:$B$6,XMATCH("*"&A2&"*", Sheet1!$A$1:$A$6,2))
C2:C11	C2	=TEXTJOIN(", ",,FILTER(Sheet1!$B$1:$B$6,ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$6)),""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C11	Expression	=COUNTIFS($C2,"*Bullets 5*")	text	NO
A2:C11	Expression	=COUNTIFS($C2,"*Listing title*")	text	NO

 

[eaxlns]

Can you please tell me how to set up the conditional formatting? Thank you