Function for filtering

**dsteele111** · 01-23-2019, 01:15 PM

Hello all,

I see that excel is releasing a "FILTER" function, however, my edition does not have this neat tool.

I am working with a list of recipes and I would like to create a filter to filter out recipes that contain certain specified ingredients.

For example, I may have 5 different recipes named "Recipe 1", "Recipe 2", "Recipe 3", "Recipe 4", and "Recipe 5".

I could have a column that contains the ingredients to each recipe.
For example, "Recipe 1" may contain eggs, flour, and milk. "Recipe 2" may contain lettuce, tomato, and onion. "Recipe 3" may contain nuts etc...

I could then have a list of ingredients that I do not want, for example (eggs and nuts). I would like to retrieve a list of the recipes available that filters out the ingredients that I do not want (eggs and nuts).

What function could I use that would allow me to search through all of the ingredients for any of the items that I am trying to filter out (eggs and nuts), and then retrieve the names of the recipes that I am trying to avoid ("Recipe 1" & "Recipe 3") as well as a list of the recipes that I am trying to keep ("Recipe 2").

Thank you very much for your help!!

**Pete_UK** · 01-23-2019, 01:35 PM

I would use a helper column in your main sheet, which identifies those recipes which contain ingredients on your list and allocates a unique code to each. Then in your subsidiary sheets you can make use of an INDEX/MATCH formula to retrieve the appropriate records.

It would help if you attached a sample Excel workbook, then I could tailor the solution to suit your particular layout.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

**dsteele111** · 01-24-2019, 08:25 AM

Hello Pete,

Thank you very much for your kind reply yesterday.

I have attached to this post a sample excel sheet.

Column A are the ingredients for the corresponding recipe in Column B.

Column D is a list of ingredients that I can modify to avoid. (I highlighted the foods in the avoid list that are actually within the recipes just to make it easier).

I would like column E and Column F to automatically produce the results that I manually input into the cells beneath.

Your help is greatly appreciated,

David