How to select range into a cell based on drop down selection

Question

I have two sheets in my Google spreadsheet: Meal Plan and Recipes.

Meal Plan looks like this:

Recipes looks like this:

I would like to write a formula or function which allows me to select a meal from one of the 'Items' drop downs (in 'Meal Plan'!C2:C5) and have the 'Shopping List' field in that row populated with the 'Ingredients' values from the 'Recipes' sheet where the item selected matches the 'Meal' field in column A ('Recipes'!A2:A10).

Would this be a function or a formula?

Answer 1

Select all cells of the Ingredients column from the Recipes sheet using the FILTER() function by the condition "the Meal column is exactly equal to the selected value in cell C4". Combine the filtering result into one string with TEXTJOIN() function. To make the formula work only for the selected values and ignore empty cells, wrap it in the IF() function:

=IF(C4="","", TEXTJOIN(";",1,FILTER(Recipes!$B$2:$B,Recipes!$A$2:$A=C4)))

If you want to add their cost to the names of the ingredients, then add the first parameter of the FILTER() function as follows:

=IF(C4="","", TEXTJOIN(";",1,FILTER(Recipes!$B$2:$B&" - "&Recipes!$D$2:$D,Recipes!$A$2:$A=C4)))

Answer 2

I have placed this formula in the D column cells:

=textjoin(", ",true,filter(Recipes!$B$2:$B$100,Recipes!$A$2:$A$100=C2))

Fortunately, the $ character prevents Sheets from incrementing the range references so I can select and drag that cell D2 through to D5 (much further in my actual file) and the formula stays correctly referencing the other sheet!