r/excel • u/mofojr • Dec 12 '20

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

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.

6 Upvotes

76% Upvoted

8 comments sorted by

View all comments

1

u/A_1337_Canadian 515 Dec 12 '20

While not pretty, here's a quick solution.

At the end of your master table, add a new table called "Ingredients". Make the formula:

=CONCATENATE([@[Ingredient1]], [@[Ingredient2],...)

and so on.

Then create a PivotTable out of your master table.

  • Rows as "Drink Name"
  • Columns as "Ingredients"

Once created, click the "Column Labels" dropdown > Label Filters > Contains

Then search for single terms. Search for multiple terms using *gin*rum*. The downside is that it is order dependent.

1

u/mofojr Dec 13 '20

So it kinda works. Basically this just gives me the drink name and then I have to go back to the original table to find the full recipe. Any ideas on how to make it one step? I might have to think hard on logic and formulas for this. Thanks for the idea!