Extracting Ingredients from an #Excel List by Cost Category by David Hager

Disclaimer: This technique uses the TEXTJOIN function. You need the Excel version included in Office 365 for the TEXTJOIN formula to work.

There are quite a few examples of a material based on ingredients. These include recipes, blends, paint mixtures, etc. Presented here is a method of extractiong from a list the ingredients for each mixture based on whether they have a low, medium or high cost.

The main list has 3 columns: Mixture, Ingredient and Cost. The lookup table has a list of ingredients based on cost category, as shown in the figure.

 IngredientListByCost1

The main formula from cell G2 is

=TEXTJOIN(“, “,TRUE,IF(((Mixture=$F2)+(Cost=G$1))=2,Ingredient,””))

This formula is filled to complete the lookup table.

If the mixture and the cost are correct for the formula based on its position in the table, it will return the corresponding ingredient into an array. The other array elements are left blank. Then, the TEXTJOIN function concatenates the elements of the array, excluding blanks.

I hpe that you find this useful!

You can download the file here.

IngredientListByCost

 

Advertisements

2 thoughts on “Extracting Ingredients from an #Excel List by Cost Category by David Hager

  1. Pingback: #Excel Super Links #129 – shared by David Hager | Excel For You

  2. Pingback: #Excel Super Links #130 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s