Vlookup on phrase in large block of text

eaxlns

New Member
Joined
Apr 18, 2019
Messages
15
Office Version
  1. 365
Platform
  1. MacOS
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
AB
1Mighty 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
2The 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
3Skill 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
4Unique 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
5Is 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
6For 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
1PHRASE
2sensory toys
3sensory toys for toddlers 1-3
4learning toys for toddlers 1-3
5busy board
6autism toys
7autism sensory toys
8learning toys
9toddler learning toys
10busy board for 1 year old
11toddler learning
Sheet2
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 18, 2019
Messages
15
Office Version
  1. 365
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABC
1PHRASE
2sensory toysLISTING TITLELISTING TITLE
3sensory toys for toddlers 1-3#N/A 
4learning toys for toddlers 1-3#N/A 
5busy boardLISTING TITLELISTING TITLE, BULLETS 5
6autism toys#N/A 
7autism sensory toysLISTING TITLELISTING TITLE
8learning toysBULLETS 1BULLETS 1
9toddler learning toys#N/A 
10busy board for 1 year oldBULLETS 5BULLETS 5
11toddler learningLISTING TITLELISTING TITLE
12
Sheet2
Cell Formulas
RangeFormula
B2:B11B2=INDEX(Sheet1!$B$1:$B$6,XMATCH("*"&A2&"*", Sheet1!$A$1:$A$6,2))
C2:C11C2=TEXTJOIN(", ",,FILTER(Sheet1!$B$1:$B$6,ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$6)),""))
 
Solution

eaxlns

New Member
Joined
Apr 18, 2019
Messages
15
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
Like
+Fluff 1.xlsm
AB
1PHRASE
2sensory toysLISTING TITLE
3sensory toys for toddlers 1-3#N/A
4learning toys for toddlers 1-3#N/A
5busy boardLISTING TITLE
6autism toys#N/A
7autism sensory toysLISTING TITLE
8learning toysBULLETS 1
9toddler learning toys#N/A
10busy board for 1 year oldBULLETS 5
11toddler learningLISTING TITLE
Sheet2
Cell Formulas
RangeFormula
B2:B11B2=INDEX(Sheet1!$B$1:$B$6,XMATCH("*"&A2&"*", Sheet1!$A$1:$A$6,2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B11Expression=$B2="Bullets 1"textNO
A2:B11Expression=$B2="Listing title"textNO
 

eaxlns

New Member
Joined
Apr 18, 2019
Messages
15
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
You would have to arrange the rules to suit
+Fluff 1.xlsm
ABC
1PHRASE
2sensory toysLISTING TITLELISTING TITLE
3sensory toys for toddlers 1-3#N/A 
4learning toys for toddlers 1-3#N/A 
5busy boardLISTING TITLELISTING TITLE, BULLETS 5
6autism toys#N/A 
7autism sensory toysLISTING TITLELISTING TITLE
8learning toysBULLETS 1BULLETS 1
9toddler learning toys#N/A 
10busy board for 1 year oldBULLETS 5BULLETS 5
11toddler learningLISTING TITLELISTING TITLE
Sheet2
Cell Formulas
RangeFormula
B2:B11B2=INDEX(Sheet1!$B$1:$B$6,XMATCH("*"&A2&"*", Sheet1!$A$1:$A$6,2))
C2:C11C2=TEXTJOIN(", ",,FILTER(Sheet1!$B$1:$B$6,ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$6)),""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:C11Expression=COUNTIFS($C2,"*Bullets 5*")textNO
A2:C11Expression=COUNTIFS($C2,"*Listing title*")textNO
 

eaxlns

New Member
Joined
Apr 18, 2019
Messages
15
Office Version
  1. 365
Platform
  1. MacOS
Can you please tell me how to set up the conditional formatting? Thank you
 

Forum statistics

Threads
1,144,524
Messages
5,724,842
Members
422,585
Latest member
k3n

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top