Find Unique Combination of 2 columns where both have re-occuring values

[Hanumanji]

Dear Excelisseurs,

I'm somewhat of an excel rookie so bear with me, I'm running flat with this one so I figured it's best to ask you.

Basically I want to return a value in column 2, but where the value in column 1 is equal to what I ask for.

For example in the table below, I want to be able to return "2" from table[Ingredients] where the value in table[Dish] is equal to "A" - which is easy with XLOOKUP. However I also want to be able to return "2" from table[Ingredients] where the value in table[Dish] is "B" or "C" or "D", you get my point. There might be 100s of dishes with the same ingredient, but I need to return the unique combination I ask for.

Dish	Ingredients	Qty	Unit
A	1	5	ml
A	2	7	ml
A	3	4	ml
A	4	3	ml
B	1	1	ml
B	2	10	ml
B	3	15	ml
B	4	22	ml

Thank you Kindly!

 

[Hanumanji]

Sorry, forgot to update the comment under Dish-A table

Govindas - Recipe Book.xlsx
	A	B	C	D	E	F	G	H	I
1	Dish	Ingredients	Qty	Unit		Dish-A
2	Dish-A	Brown Sugar	40	ml			BATCHES
3	Dish-A	Butter	40	g		Ingredients	4	7	10
4	Dish-A	Flour	100	g		Brown Sugar	160ml
5	Dish-A	Milk	0.5	cups		Butter
6	Dish-B	Brown Sugar	50	ml		Flour
7	Dish-B	Butter	60	g		Milk
8	Dish-B	Cream	50	ml		Cell G4 will use F4 and F1 to retrieve the value of QTY and multiply it by G3
9	Dish-B	Flour	120	g
10	Dish-C	Carrots	10	kg
11	Dish-C	Cauliflower	5	kg
12	Dish-D	Parsley	4	bunches
13	Dish-D	Potatoes	20	kg
Sheet2

Cell Formulas
Range	Formula
G4	G4	=C2*G3 & D2

 

[StephenCrump]

Here's one way:

	A	B	C	D	E	F	G	H	I
1	Dish	Ingredients	Qty	Unit		Dish-A
2	Dish-A	Brown Sugar	40	ml			BATCHES
3	Dish-A	Butter	40	g		Ingredients	4	7	10
4	Dish-A	Flour	100	g		Brown Sugar	160 ml	280 ml	400 ml
5	Dish-A	Milk	0.5	cups		Butter	160 g	280 g	400 g
6	Dish-B	Brown Sugar	50	ml		Flour	400 g	700 g	1000 g
7	Dish-B	Butter	60	g		Milk	2 cups	3.5 cups	5 cups
8	Dish-B	Cream	50	ml
9	Dish-B	Flour	120	g
10	Dish-C	Carrots	10	kg
11	Dish-C	Cauliflower	5	kg
12	Dish-D	Parsley	4	bunches
13	Dish-D	Potatoes	20	kg
Sheet1

Cell Formulas
Range	Formula
F4:F7	F4	=FILTER(B2:B13,A2:A13=F1)
G4:I7	G4	=LET(f,FILTER(C2:D13,A2:A13=F1),INDEX(f,,1)*G3:I3& " "&INDEX(f,,2))
Dynamic array formulas.

 

[Hanumanji]

Here's one way:

	A	B	C	D	E	F	G	H	I
1	Dish	Ingredients	Qty	Unit		Dish-A
2	Dish-A	Brown Sugar	40	ml			BATCHES
3	Dish-A	Butter	40	g		Ingredients	4	7	10
4	Dish-A	Flour	100	g		Brown Sugar	160 ml	280 ml	400 ml
5	Dish-A	Milk	0.5	cups		Butter	160 g	280 g	400 g
6	Dish-B	Brown Sugar	50	ml		Flour	400 g	700 g	1000 g
7	Dish-B	Butter	60	g		Milk	2 cups	3.5 cups	5 cups
8	Dish-B	Cream	50	ml
9	Dish-B	Flour	120	g
10	Dish-C	Carrots	10	kg
11	Dish-C	Cauliflower	5	kg
12	Dish-D	Parsley	4	bunches
13	Dish-D	Potatoes	20	kg
Sheet1

Cell Formulas
Range	Formula
F4:F7	F4	=FILTER(B2:B13,A2:A13=F1)
G4:I7	G4	=LET(f,FILTER(C2:D13,A2:A13=F1),INDEX(f,,1)*G3:I3& " "&INDEX(f,,2))
Dynamic array formulas.

Thank you StephenCrump, although it is a neat solution my end use-case gets a tad bit more complex..
Parts of a dish may be prepared by multiple roles in the kitchen, therefore putting out an array in a predetermined range will not satisfy the required condition.

• Goal: Ideally single-cell formula returning required value

This is another hardcoded example

	A	B	C	D	E	F	G	H	I
1	Dish	Ingredients	Qty	Unit		Kitchen Hand
2	Dish-A	Brown Sugar	40	ml		Ingredients	Mon	Tue	Wed
3	Dish-A	Butter	40	g		Carrots	37.5 kg	37.5 kg	37.5 kg
4	Dish-A	Flour	100	g		Potatoes	80 kg	0 kg	170 kg
5	Dish-A	Milk	0.5	cups		Milk	5 cups	6 cups	5 cups
6	Dish-B	Brown Sugar	50	ml
7	Dish-B	Butter	60	g		Cook Aide
8	Dish-B	Cream	50	ml		Ingredients	Mon	Tue	Wed
9	Dish-B	Flour	120	g		Cauliflower	50 kg	60 kg	50 kg
10	Dish-C	Carrots	2.5	kg		Parsley	4 bunches	0 bunches	8.5 bunches
11	Dish-C	Cauliflower	5	kg		Butter	400 g	480 g	400 g
12	Dish-D	Parsley	0.5	bunches
13	Dish-D	Potatoes	10	kg		Batches
14						Dishes	Mon	Tue	Wed
15						Dish-A	10	12	10
16						Dish-C	15	15	15
17						Dish-D	8	0	17
Sheet2

Cell Formulas
Range	Formula
F3	F3	=B10
G3	G3	=G16*C10 & " " & D10
H3	H3	=H16*C10 & " " & D10
I3	I3	=I16*C10 & " " & D10
F4	F4	=B13
G4	G4	=G17*C13 & " " & D13
H4	H4	=H17*C13 & " " & D13
I4	I4	=I17*C13 & " " & D13
F5	F5	=B5
G5	G5	=G15*C5 & " " & D5
H5	H5	=H15*C5 & " " & D5
I5	I5	=I15*C5 & " " & D5
F9:F10	F9	=B11
G9	G9	=G15*C11 & " " & D11
H9	H9	=H15*C11 & " " & D11
I9	I9	=I15*C11 & " " & D11
G10	G10	=G17*C12 & " " & D12
H10	H10	=H17*C12 & " " & D12
I10	I10	=I17*C12 & " " & D12
F11	F11	=B3
G11	G11	=G15*C3 & " " & D3
H11	H11	=H15*C3 & " " & D3
I11	I11	=I15*C3 & " " & D3

 

[Hanumanji]

I thought about it, experimented a little and managed to develop a solution that achieves what I'm after by using XLOOKUP with FILTER.

I don't know if it's a pretty solution, feedback is welcome. I've just included a simple example, but it works as intended for the sheets I have.

Thank you everyone for the help so far, much appreciated!

	A	B	C	D	E	F	G	H	I	J
1	Dish	Ingredients	Qty	Unit				Batches
2	Dish-A	Brown Sugar	40	ml				5	10	15
3	Dish-A	Butter	40	g		Dish-B	Cream	250 ml	500 ml	750 ml
4	Dish-A	Flour	100	g
5	Dish-A	Milk	0.5	cups
6	Dish-B	Brown Sugar	50	ml
7	Dish-B	Butter	60	g
8	Dish-B	Cream	50	ml
9	Dish-B	Flour	120	g
10	Dish-C	Carrots	2.5	kg
11	Dish-C	Cauliflower	5	kg
12	Dish-D	Parsley	0.5	bunches
13	Dish-D	Potatoes	10	kg
Sheet1

Cell Formulas
Range	Formula
H3	H3	=XLOOKUP(G3,FILTER(Table5[Ingredients],Table5[Dish]=F3),FILTER(Table5[Qty],Table5[Dish]=F3))*H2 & " " & XLOOKUP(G3,FILTER(Table5[Ingredients],Table5[Dish]=F3),FILTER(Table5[Unit],Table5[Dish]=F3))
I3	I3	=XLOOKUP(G3,FILTER(Table5[Ingredients],Table5[Dish]=F3),FILTER(Table5[Qty],Table5[Dish]=F3))*I2 & " " & XLOOKUP(G3,FILTER(Table5[Ingredients],Table5[Dish]=F3),FILTER(Table5[Unit],Table5[Dish]=F3))
J3	J3	=XLOOKUP(G3,FILTER(Table5[Ingredients],Table5[Dish]=F3),FILTER(Table5[Qty],Table5[Dish]=F3))*J2 & " " & XLOOKUP(G3,FILTER(Table5[Ingredients],Table5[Dish]=F3),FILTER(Table5[Unit],Table5[Dish]=F3))

 

[Peter_SSs]

Would this be any use?

Hanumanji.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Dish	Ingredients	Qty	Unit				Batches
2	Dish-A	Brown Sugar	40	ml				5	10	15
3	Dish-A	Butter	40	g		Dish-B	Cream	250 ml	500 ml	750 ml
4	Dish-A	Flour	100	g
5	Dish-A	Milk	0.5	cups
6	Dish-B	Brown Sugar	50	ml
7	Dish-B	Butter	60	g
8	Dish-B	Cream	50	ml
9	Dish-B	Flour	120	g
10	Dish-C	Carrots	2.5	kg
11	Dish-C	Cauliflower	5	kg
12	Dish-D	Parsley	0.5	bunches
13	Dish-D	Potatoes	10	kg
14
Sheet1

Cell Formulas
Range	Formula
H3:J3	H3	=LET(f,FILTER(Table5,(Table5[[Dish]:[Dish]]=$F3)*(Table5[[Ingredients]:[Ingredients]]=$G3)),INDEX(f,3)*H2&" "&INDEX(f,4))

 

[Hanumanji]

Would this be any use?

Hanumanji.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Dish	Ingredients	Qty	Unit				Batches
2	Dish-A	Brown Sugar	40	ml				5	10	15
3	Dish-A	Butter	40	g		Dish-B	Cream	250 ml	500 ml	750 ml
4	Dish-A	Flour	100	g
5	Dish-A	Milk	0.5	cups
6	Dish-B	Brown Sugar	50	ml
7	Dish-B	Butter	60	g
8	Dish-B	Cream	50	ml
9	Dish-B	Flour	120	g
10	Dish-C	Carrots	2.5	kg
11	Dish-C	Cauliflower	5	kg
12	Dish-D	Parsley	0.5	bunches
13	Dish-D	Potatoes	10	kg
14
Sheet1

Cell Formulas
Range	Formula
H3:J3	H3	=LET(f,FILTER(Table5,(Table5[[Dish]:[Dish]]=$F3)*(Table5[[Ingredients]:[Ingredients]]=$G3)),INDEX(f,3)*H2&" "&INDEX(f,4))

Thank you, yeah that is a clean solution and it also helps me with a few other cases. Much appreciated!