I've been wracking my brain trying to figure out this problem using IF and VLOOKUP but I've given in and decided to ask for help.

I have 2 tabs in a workbook, Recipes and Ingredients. Each has a pretty big table and I need to update it occasionally. I'll include a short sample.

Ingredients is a list of ingredients and their properties, each one assigned a code and the amount used is listed. Ingredients Recipes is where I assemble a recipe by listing its ingredient codes. The rest of the table is VLOOKUP getting information from Ingredients. Recipe

Sometimes I need to update a recipe, but I have to individually search for each Ingredient ID within it in order to update the ingredients' properties otherwise the VLOOKUP will by overwritten.

I want to add a column in the the Ingredients tab that will let me filter by which recipe they appear in. So either somehow getting this information and assembling it (Mock 1) or having a cell I can add a recipe number into and each ingredient will have a helper column that shows a true or false that I can filter by (Mock 2).

Could anyone help me figure out how to do this?

Thanks in advance.

CodePudding user response：

As I do not have your exact columns:

=TEXTJOIN(", ";1;FILTER($A$1:$A$100;$C$1:$C$100=E1))

• column A would be Recipe No.
• Column C would be Ingredients ID, both in the recipe table
• cell E1 is the Ingredients ID by respective row in the ingredients table

If needed, SORT(FILTER()) can ensure ascending or descending order before the TEXTJOIN.

P.S. I hope there is no typo, but I think you understand the logic to adjust accordingly. Also may need to replace ; with , based on regional settings