Lookup column reference, return list of non-blank values in table?

[SarahLouM]

Hello - I'm struggling with this one and my basic excel knowledge.
I have a list of recipes and ingredients - I'd like to put these into table format, and then have a formula to lookup a certain recipe number and return the ingredients and quantities used in that recipe.
I've put recipe numbers as headers and ingredients down the first column purely because it allows me to add recipes to the main table more easily - can change this if required.
For example:

	1.0	1.1	1.2
Apples	1000		100
Pears		100
Sugar	500	200	100
Chocolate		100	50

Ideally, I'd like to create a sheet where I can enter "1.0" and return a list of ingredients used in recipe 1.0 and their quantities. Then if this cell is changed to recipe 1.1, it updates to that recipe info, etc.
I want it to ignore any ingredients that have blank values.

Does anyone have any tips for this? I've cobbled together formulas that can look up specific ingredients and return their value, but not managed to figure out something that will return a whole list based off looking up another column.
Does my table layout need to be flipped or changed to allow this?
I'm using excel 365.

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this

21 11 10.xlsm
	A	B	C	D	E	F	G	H
1		1.0	1.1	1.2			Recipe	Ingredients
2	Apples	1000		100			1.2	Apples
3	Pears		100					Sugar
4	Sugar	500	200	100				Chocolate
5	Chocolate		100	50
6
Recipes

Cell Formulas
Range	Formula
H2:H4	H2	=FILTER(A2:A5,FILTER(B2:D5,B1:D1=G2)<>"")
Dynamic array formulas.

 

[Peter_SSs]

Hmm, you probably want the quantities too?

21 11 10.xlsm
	A	B	C	D	E	F	G	H	I
1	Ingredients	1.0	1.1	1.2			Recipe
2	Apples	1000		100		Ingredients	1.2	Apples	100
3	Pears		100					Sugar	100
4	Sugar	500	200	100				Chocolate	50
5	Chocolate		100	50
6
Recipes (2)

Cell Formulas
Range	Formula
H2:I4	H2	=LET(f,FILTER(A2:D5,COUNTIF(F2:G2,A1:D1)),FILTER(f,INDEX(f,0,2)>0))
Dynamic array formulas.

 

[SarahLouM]

Hmm, you probably want the quantities too?

21 11 10.xlsm
	A	B	C	D	E	F	G	H	I
1	Ingredients	1.0	1.1	1.2			Recipe
2	Apples	1000		100		Ingredients	1.2	Apples	100
3	Pears		100					Sugar	100
4	Sugar	500	200	100				Chocolate	50
5	Chocolate		100	50
6
Recipes (2)

Cell Formulas
Range	Formula
H2:I4	H2	=LET(f,FILTER(A2:D5,COUNTIF(F2:G2,A1:D1)),FILTER(f,INDEX(f,0,2)>0))
Dynamic array formulas.

Fantastic - This should work!
Does this formula rely on the 'ingredients' text in F2? What part of the formula would I amend if I wanted to move this to another cell?

Thanks for your help

 

[Peter_SSs]

Does this formula rely on the 'ingredients' text in F2?

It does rely on that text but it does not have to be in F2. Ideally it does need to be near the recipe number though. Here are a couple of other workable arrangements.

21 11 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Ingredients	1.0	1.1	1.2			Recipe	1.2	Ingredients		1.2
2	Apples	1000		100				Apples	100		Ingredients	Apples	100
3	Pears		100					Sugar	100			Sugar	100
4	Sugar	500	200	100				Chocolate	50			Chocolate	50
5	Chocolate		100	50
Recipes (3)

Cell Formulas
Range	Formula
H2:I4	H2	=LET(f,FILTER(A2:D5,COUNTIF(H1:I1,A1:D1)),FILTER(f,INDEX(f,0,2)>0))
L2:M4	L2	=LET(f,FILTER(A2:D5,COUNTIF(K1:K2,A1:D1)),FILTER(f,INDEX(f,0,2)>0))
Dynamic array formulas.

If it is a visual thing that you don't like it, you could say leave it in F2 like I had it earlier and just hide column F.

21 11 10.xlsm
	A	B	C	D	E		G	H	I
1	Ingredients	1.0	1.1	1.2			Recipe
2	Apples	1000		100			1.2	Apples	100
3	Pears		100					Sugar	100
4	Sugar	500	200	100				Chocolate	50
5	Chocolate		100	50
Recipes (2)

Cell Formulas
Range	Formula
H2:I4	H2	=LET(f,FILTER(A2:D5,COUNTIF(F2:G2,A1:D1)),FILTER(f,INDEX(f,0,2)>0))
Dynamic array formulas.