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

Meal Plan looks like this:

enter image description here

Recipes looks like this:

enter image description here

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?

  • 1
    Of course it will be formula like =IF(C4="","", TEXTJOIN(";",1,FILTER(Recipes!$B$2:$B,Recipes!$A$2:$A=C4))) or if you need cost =IF(C4="","", TEXTJOIN(";",1,FILTER(Recipes!$B$2:$B&" - "&Recipes!$D$2:$D,Recipes!$A$2:$A=C4)))
    – JohnSUN
    Commented Sep 5, 2022 at 10:06
  • I finally worked something out like that - please post yours as an answer :)
    – Matt W
    Commented Sep 5, 2022 at 10:20

2 Answers 2


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)))

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.