0

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.

Improve this question
4
  • 1
    FILTER the recipe list against the respective ingredients ID will give you the rows of recipes incl. the ingredients. TEXTJOIN can transform the filtered column of the recipe ID into a text with a delimiter of your choice as in Mock 1 Commented Jul 11, 2022 at 11:19
  • 1
    Feel free to post your formula if you encounter any issue. You may want to consider to replace VLOOKUP with XLOOKUP, among other improvements it can return multiple columns at once. Commented Jul 11, 2022 at 11:48
  • Thanks for your help. I think the FILTER function will spill out more data right? That wouldn't really work for my spreadsheet. Commented Jul 11, 2022 at 12:04
  • 1
    Yes, FILTER spills, but if you nest it inside TEXTJOIN in one formula, you can keep the result contained to 1 cell. Commented Jul 11, 2022 at 12:21

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Perfect! Thank you kind anonymous stranger!
No worries. Feel free to confirm/vote the answer if it solved your question ;-)

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.