XLOOKUP (multiple criteria) not working

[k3yn0t3]

Hi there. Could someone kindly assist me with an XLOOKUP formula I'm using to pull in data from a master tab to another sheet? I want to pull in data onto "Store 1" sheet based on:

• Store Number
• month
• year
• line item name (revenue, COGS, etc.)

=+XLOOKUP(1,('Data Master'!$B$7:$FZ$7='Store 1'!$B26)*('Data Master'!6:6='Store 1'!H$23)*('Data Master'!5:5='Store 1'!H$22),'Data Master'!R8:NA10)

Thank you and please find examples of my two sheets below:

Single Store Template Sheet
^I need to write formula to fill in the below table based on the "Master Database" Sheet's data
*This Sheet will only show Store 1 data

Store	1
Status
Opened
Closed
ST
Dept
Sq FT						1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12
						2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2017	2017	2017	2017	2017	2017	2017	2017	2017	2017	2017	2017 (a)	2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019
Revenue
COGS						​

Master Database Sheet
^I need to pull data from here. Everything is hardcoded

									1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12
									2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2017	2017	2017	2017	2017	2017	2017	2017	2017	2017	2017	2017 (a)	2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019
Store	Status	Opened	Closed	ST	Dept	Sq FT			Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue	Revenue
1	Oen	1/1/2000	na	NY	East	5000​			10,000	20,000	21,000	22,000	23,000	24,000	25,000	26,000	27,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000
2	Oen	1/1/2001	na	NY	East	6000​			11,000	21,000	22,000	23,000	24,000	25,000	26,000	27,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000	67,000
3	Oen	1/1/2002	1/1/2006	NY	East	7000​			12,000	22,000	23,000	24,000	25,000	26,000	27,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000	67,000	68,000
4	Oen	1/1/2003	na	NY	East	8000​			13,000	23,000	24,000	25,000	26,000	27,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000	67,000	68,000	69,000
6	Oen	1/1/2004	na	NY	East	9000​			14,000	24,000	25,000	26,000	27,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000	67,000	68,000	69,000	70,000
7	Oen	1/1/2005	1/1/2009	CA	West	10000​			15,000	25,000	26,000	27,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000	67,000	68,000	69,000	70,000	71,000
8	Oen	1/1/2006	na	CA	West	11000​			16,000	26,000	27,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000	67,000	68,000	69,000	70,000	71,000	72,000
9	Oen	1/1/2007	na	CA	West	12000​			17,000	27,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000	67,000	68,000	69,000	70,000	71,000	72,000	73,000
10	Oen	1/1/2008	1/1/2012	CA	West	13000​			18,000	28,000	29,000	30,000	31,000	32,000	33,000	34,000	35,000	36,000	37,000	38,000	39,000	40,000	41,000	42,000	43,000	44,000	45,000	46,000	47,000	48,000	49,000	50,000	51,000	52,000	53,000	54,000	55,000	56,000	57,000	58,000	59,000	60,000	61,000	62,000	63,000	64,000	65,000	66,000	67,000	68,000	69,000	70,000	71,000	72,000	73,000	74,000

 

[Alex Blakenburg]

It would be helpful if you used the XL2BB, we are having to guess as to where your columns and rows are.
You are going to need 2 lookups one for the rows and one for the columns. This could be 2 Xlookups 2 filter or a mix. I find using Index match for one of the easier to follow.
If your have exactly the same number of Year/Month/Revenue columns you could cut down on the criteria and just spill the Revenue or COGS columns

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Store	1
2	Status
3	Opened
4	Closed
5	ST
6	Dept
7	Sq FT						1	2	3	4	5	6	7	8	9
8							2016	2016	2016	2016	2016	2016	2016	2016	2016
9	Revenue		Option 1 - assume columns are exactly the same (all columns and same order) SPILL				10,000	20,000	21,000	22,000	23,000	24,000	25,000	26,000	27,000
10	Revenue		Option 2 - Lookup specific columns				10,000	20,000	21,000	22,000	23,000	24,000	25,000	26,000	27,000
Store 1

Cell Formulas
Range	Formula
G9:BB9	G9	=FILTER( INDEX('Data Master'!$J$8:$BE$16,MATCH(B1,'Data Master'!$A$8:$A$16,0),0), 'Data Master'!$J$7:$BE$7=$A9,0)
G10:O10	G10	=FILTER( INDEX('Data Master'!$J$8:$BE$16,MATCH($B$1,'Data Master'!$A$8:$A$16,0),0), ('Data Master'!$J$5:$BE$5=G$7)*('Data Master'!$J$6:$BE$6=G$8)*( 'Data Master'!$J$7:$BE$7=$A10), 0)
Dynamic array formulas.

 

[k3yn0t3]

Thank you, Alex. Unfortunately, having trouble with the add-in (have spent an hour trying to install and troubleshoot it).

Your solution doesn't seem to work, but this is due to my lack of context.

On the master tab, I should mention that it's seven line items from an income statement laid out in columns side-by-side...so 48*7= 336 columns in a row

Revenue (Columns R to BM, so 48 columns, 12 months * 4 years), then a two-column gap (BN, BO), then Costs (Columns BP-DK)...this happens for 7 line items that will all be different rows on "Store 1" Sheet....

So you have the same month / year combinations shown many times over, the only difference is the "Revenue" "Costs" "Labor" designation.

Thank you again for your time. Really struggling to wrap my head around this.

 

[k3yn0t3]

I was able to play around with your formula and figure it. Thank you, again, Alex.