I have a sheet with recipes and another sheet with ingredient and available products from different suppliers. I would like to create drop-down menu's so that someone can choose the exact product that corresponds to the ingredient in the recipe.
Here is a table with an example of a simple recipe and the eventual product drop-down:
| Ingredients | Product choice |
|---|---|
| Bread | |
| Hamburger | |
| Cheese | |
| Bacon |
And here is a table example of the ingredients with possible products:
| Ingredient | Product 1 | Product 2 | Product 3 | Product 4 |
|---|---|---|---|---|
| Bread | Dist 1 - Hamburger Bun | Dist 2 - Potato Roll | ||
| Hamburger | Dist 1 - Black Angus | Dist 1 - Mushroom | Dist 2 - Black Angus | Dist 2 - Vegan |
| Cheese | Dist 1 - Cheddar | Dist 2 - Gouda | Dist 3 - Blue | |
| Bacon | Dist 1 - Smoked bacon | Dist 1 - Bacons cubes | Dist 1 - Vegan Bacon |
What I would ideally like to have is then the drop-down menu that gives me only the available products for the different ingredients. Something like this:
| Ingredients | Product choice |
|---|---|
| Bread | - Dist 1 - Hamburger Bun - Dist 2 - Potato Roll |
| Hamburger | - Dist 1 - Black Angus - Dist 1 - Mushroom - Dist 2 - Black Angus - Dist 2 - Vegan |
| Cheese | - Dist 1 - Cheddar - Dist 2 - Gouda - Dist 3 - Blue |
| Bacon | - Dist 1 - Smoked bacon - Dist 1 - Bacons cubes - Dist 1 - Vegan Bacon |
Now I understand how to make the lists, but not how to combine and INDEX MATCH like function to do this to create the lists, and doing it all by hand would take an impossible amount of time.
CodePudding user response：
Assuming that your products table is in G1:K5 (with headers in row 1), and that the first ingredient for which you wish to apply the validation is in A2, first make sure that the active cell within the worksheet is somewhere in row 2 and then go to Name Manager and define MyVal as:
=LET(ζ,INDEX($H$2:$K$5,MATCH($A2,$G$2:$G$5,0),),INDEX(ζ,1):INDEX(ζ,COUNTA(ζ)))
After which you can apply the following formula as a Data Validation List to cell B2:
=MyVal
This validation can be then copied down to B3, B4, etc.