SUMIFS + INDIRECT (Dynamic)

[s_1592]

Hi!
I'm trying to obtain the consolidate data (in tab Conso) for Revenue and COGS from two tabs (Los Angeles and New York) via a SUMIFS+INDIRECT, fed by column mapping references (indicated with the red arrows).

I however cannot find a way to make the formula dynamic.

Thank you in advance for your help.

Tab 1 - Los Angeles

Tab 2 - New York

Tab 3 - Conso

 

[ExceLoki]

here's a couple of ways that might work for you. went a kinda different route. let me know if this works for what you're trying to do.
---------------------

Book1
	A	B	C	D	E	F
2	Corp A+B		Jan-22	Feb-22	Mar-22	Q1
3		Revenue	650	380	800	1830
4		COGS	-40	-80	-160	-280
5		GM	610	300	640	1550
6
7
8	Corp A+B		Jan-22	Feb-22	Mar-22	Q1
9		Revenue	650	380	800	1830
10		COGS	-40	-80	-160	-280
11		GM	610	300	640	1550
Conso

Cell Formulas
Range	Formula
C3:E3	C3	=SUM(HLOOKUP(C2,'Los Angeles'!$C$2:$F$4,2),HLOOKUP(C2,'Los Angeles'!$C$7:$F$9,2),HLOOKUP(C2,'New York'!$D$2:$G$4,2),HLOOKUP(C2,'New York'!$D$7:$G$9,2))
F3:F4,F9:F10	F3	=SUM(C3:E3)
C4:E4	C4	=SUM(HLOOKUP(C2,'Los Angeles'!$C$2:$F$4,3),HLOOKUP(C2,'Los Angeles'!$C$7:$F$9,3),HLOOKUP(C2,'New York'!$D$2:$G$4,3),HLOOKUP(C2,'New York'!$D$7:$G$9,3))
C5:F5,C11:F11	C5	=SUM(C3:C4)
C9:E10	C9	=XLOOKUP(B9,'Los Angeles'!$B$3:$B$4,'Los Angeles'!$C$3:$E$4)+XLOOKUP(B9,'Los Angeles'!$B$8:$B$9,'Los Angeles'!$C$8:$E$9)+XLOOKUP(B9,'New York'!$C$3:$C$4,'New York'!$D$3:$F$4)+XLOOKUP(B9,'New York'!$C$8:$C$9,'New York'!$D$8:$F$9)
Dynamic array formulas.