Lookup across multiple dynamic array columns

[Tashat]

Hi All

I'm looking for help to perform a lookup across three columns. These are all dynamic arrays, so could all change in length. I've managed to stack three columns together as shown in column L below. I am now looking for a formula for column L whereby the entry in Column K is looked up in either column B, column E, or column I and the count from the adjacent column (C, F, or I) is returned. I've tried merging IFERROR and SUMPRODUCT, but it only works for one of the arrays depending on the order I enter the formula, so this doesn't work. The whole sheet is getting quite ugly unfortunately. The counts in columns C, F and J are generated using CountIf from elsewhere in the Workbook, but this fruit and veg example is just a small version I've generated for the purposes of asking on here. Any help would be much appreciated.

Fruit Example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Fruits Alphabetical	Fruit Concat	Fruit Count	Veg	Veg Concat	Veg Count	Other Food	Other Food Sort	Other Food Array	Other Food Count	Stacked List	Stacked Count Lookup
2	Apples	Whole Apples	5	Carrots	Whole Carrots	2	Cheese	*Individual Item	*Individual Item	1	*Individual Item	1
3	Banana	Whole Banana	3	Potatoes	Whole Potatoes	3	Bread	Bread	Bread	4	Bread	4
4	Blueberries	Whole Blueberries	4	Swede	Whole Swede	1	Butter	Butter	Butter	2	Butter	2
5	Grapes	Whole Grapes	1	Turnips	Whole Turnips	3	Ketchup	Cereal	Cereal	5	Cereal	5
6	Melon	Whole Melon	3				*Individual Item	Cheese	Cheese	3	Cheese	3
7	Pears	Whole Pears	2				Cereal	Ketchup	Ketchup	1	Ketchup	1
8	Pineapple	Whole Pineapple	1				Milk	Milk	Milk	3	Milk	3
9	Strawberries	Whole Strawberries	2								Whole Carrots	2
10											Whole Potatoes	3
11											Whole Swede	1
12											Whole Turnips	3
13											Whole Apples	5
14											Whole Banana	3
15											Whole Blueberries	4
16											Whole Grapes	1
17											Whole Melon	3
18											Whole Pears	2
19											Whole Pineapple	1
20											Whole Strawberries	2
Sheet2

Cell Formulas
Range	Formula
A2:A9	A2	=SORT(FruitList)
B2:B9,E2:E5	B2	="Whole "&A2#
D2:D5	D2	=SORT(Veg_LIst)
H2:H8	H2	=SORT(OtherFood)
I2:I8	I2	=H2#
K2:K20	K2	=LET(range1,I2#,range2,Veg_List_Sort,range3,B2#,rows1,ROWS(range1),rows2,ROWS(range2),rows3,ROWS(range3),cols1,COLUMNS(range1),rowindex,SEQUENCE(rows1+rows2+rows3),colindex,SEQUENCE(1,cols1),IF(rowindex<=rows1,INDEX(range1,rowindex,colindex),IF(rowindex<=(rows1+rows2),INDEX(range2,rowindex-rows1,colindex),INDEX(range3,rowindex-(rows1+rows2),colindex))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
FruitList	=Sheet3!$A$2:INDEX(Sheet3!$A:$A,COUNTA(Sheet3!$A:$A))	A2:B2
OtherFood	=Sheet2!$G$2:INDEX(Sheet2!$G:$G,COUNTA(Sheet2!$G:$G))	H2
OtherFoodArray	=Sheet2!$I$2:INDEX(Sheet2!$I:$I,COUNTA(Sheet2!$I:$I))	K2
Veg_LIst	=Sheet3!$B$2:INDEX(Sheet3!$B:$B,COUNTA(Sheet3!$B:$B))	D2
Veg_List_Sort	=Sheet2!$E$2:INDEX(Sheet2!$E:$E,COUNTA(Sheet2!$E:$E))	K2

 

[jasonb75]

I can't test it because I don't have the source data for the names ranges, see if this works.

=SUMIFS(C:J,B:I,K2#)

 

[Tashat]

I can't test it because I don't have the source data for the names ranges, see if this works.

=SUMIFS(C:J,B:I,K2#)

Wow! That is brilliant. I was definitely overcomplicating it! Thank you so much.