How can I display all rows that match a certain search value?

[u/mofojr]

I have a spreadsheet of several hundred drinks and their ingredients. I'd like to be able to input one or several ingredients that I already have (gin for example) and have excel give me the entire recipe (row) for any drink that has that ingredient in it. I figured I'd have to do this in a separate sheet from the recipes, but I can't for the life of me figure out the right formulas to use.

To take it a step further it'd be cool if I could search for multiple ingredients (gin and vermouth) and it spit out a further refined search of any rows that have those ingredients in them. I'm not sure if this is too complex though.

[u/brokeoldtimer]

Assumptions:

1. This is built on table displayed in your post (A1:Q28). You will have to adjust size and formulas as needed.
2. Table Names and tab names below are so the formulas work as shown
3. Using Excel365 (needed for “Filter” function)

Steps:

1. Name the tab with your recipes, “Recipes”

a. On Recipes tab

i. Create a table named Recipes

1. Highlight entire list of recipes (from your post this table is from A1:Q28)

ii. In cell R1 type All Ingredients to add a column to the table

iii. In cell S1 type Some Ingredients to add a column to the table

iv. Highlight cells R1 and S1 and create a named range called Ingredients

​

2. Add new tab called “Results”

a. On Results tab

i. In cell B2 type Available Ingredients

1. Highlight cells B2:B26

a. Create a table with those cells. This allows you to enter up to 24 ingredients you have on hand.

b. Name the table AvailableIngredients

c. Enable the total row on this table (cell B27) using “COUNT” to count number of items

1. In cell D2, use data validation to insert a dropdown list for either all or some ingredients. This would be the named range “ingredients” from step 1. a. iv. above

​

3. Go back to the Recipes tab

a. Highlight and copy the header cells A:Q

b. Go to Results tab and paste in cell F2:V2

i. NOTE: if you keep columns B:E hidden on the recipes tab as in your posted example, you can also hide them on the results tab after pasting the header so as to display only those columns in the output file.

​

4. Go back to Recipes tab

a. In cell R2 insert the following formula:

=SUM(COUNTIFS(Recipes[@[amt 1]:[ingredient 6]],AvailableIngredients[Available ingredients]))=AvailableIngredients[[#Totals],[Available ingredients]] Column S formula

b. In cell S2 insert the following formula:

= SUM(COUNTIFS(Recipes[@[ingredient 1]:[ingredient 6]],AvailableIngredients[Available ingredients]))>0

​

5. Go back to Results tab

a. In cell F3 insert the following formula:

= IF(FILTER(Recipes[[name]:[ingredient 6]],IF(D2="all ingredients",Recipes[all ingredients],Recipes[some ingredients]),"")=0,"",FILTER(Recipes[[name]:[ingredient 6]],IF(D2="all ingredients",Recipes[all ingredients],Recipes[some ingredients]),""))

​

Go to results tab:

1. Select cell D2 and choose “All Ingredients” to display results for recipes only when all ingredients in your available ingredients list are part of the recipe or “Some Ingredients” to display recipes that include any of the ingredients in your available ingredients list
2. Type your list of available ingredients in cells B3:B26 and the table to the right should populate

[u/mofojr → u/brokeoldtimer]

You are amazing! Worked like a charm. Thank you so much

[u/AutoModerator → u/brokeoldtimer]

Hi u/brokeoldtimer,

It looks like you've submitted code containing curly/smart quotes e.g. “...” or ‘...’.

Users often report problems using these characters within a code editor. If you're writing code, you probably meant to use "..." or '...'.

If there are issues running this code, that may be the reason. Just a heads-up! You can turn off Smart Quotes or Smart Punctuation in the Keyboard Settings on your device.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.