multiple vlookups or find and replace or?

[emily7]

Hello,

I have a column with many rows each filled with different descriptions. I want to find words within the row and if a certain word is found then separate it out into different categories. The idea is similar to a vlookup in Excel, however, because there’s multiple words within each cell that matches, vlookup only return the first match.

For example:

Words	Category
Breakfast	a
Lunch	a
Dinner	a
social	b
party	b

Data	Amount	Category A	Category B
social bagel breakfast	$10	True	True
lunch break	$3	True	False
Team activity	$18	False	False

Thanks!

[echuong1 · Accepted solution]

I suggest separating out the values into a separate line, then doing the match.

This may not be the best way, but here's how I'd approach it.

[emily7 · Accepted solution]

Thank you! I'll give it a test with my file that has more rows, but it's looking lovely.

[emily7]

Hello,

Is there a way to modify the workflow where it'll accept (return as true) a variation of the word within the data? For example, the word "lunch" is within the "luncheon meeting" description. The workflow doesn't account for it. I'll need to include "luncheon" in my word list in order to pick it up, which will make it more complicated as words that are plural or abbreviated will not be picked up. 

Thanks!

[echuong1]

If you want to account for partial matches, use the find and replace instead of join.