CODEWITHSUNDEEP
vbaexceltextexcel-formula
surfrider
surfrider

Reputation: 21

Check if one of multiple values is present in a column

I have a table in Excel 2013 that has has thousands of records of food items (Beef-frozen, beef-chilled, beef-brisket, beef-ribs, chicken-fillet, chicken-whole, fish-skinned, fish-whole, yogurt, lettuce-imported, lettuce-frozen, tomato-fresh,tomato, water, milk,...etc) stored in column A. Notice the value may contain other content than the food item name.

I created column B next to column A. I want column B to hold the category of the food item in column A. For example, if A1 has in it "Beef" or "Chicken" or "Fish" then B1 should equal "Meat". If A1 has in it "Tomato" or "Lettuce" or "Onion" then B1 should equal "Vegetable".

What is the best way to achieve it?

Upvotes: 0

Views: 1846

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60414

Set up a two column table. Name it, for example FoodTable. Have the first column Named Word (for keyword) and the second column Type, for the type of product. Something like this:

enter image description here

Then, with your data in column A, enter the following formula in B1 and fill down:

=LOOKUP(2,1/ISNUMBER(FIND(FoodTable[Word],A1)),FoodTable[Type])

Results:

enter image description here

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

Assuming you have column headers, enter this formula in cell B2:

=REPT("Meat",MAX(IFERROR(MATCH({"*beef*","*chicken*","*fish*"},A2,),)))    &    REPT("Vegetable",MAX(IFERROR(MATCH({"*tomato*","*lettuce*","*onion*"},A2,),)))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Now copy B2 and select B3 down as far as you need and paste.

Note: please look closely at the big gap in the middle of the formula. You'll see that this is really two separate formulas concatenated together with an ampersand. You can easily extend this formula in the same way by adding another phrase similar to the first two for a new category. In fact, you could add many more categories in this fashion.

Upvotes: 1

Related Questions