Index match on pivot table with same column names

[stefanh]

Hello guys - I am new to posting here but have been a frequent user of the forum for years, has helped me a lot throughout the years. I am trying to do an Index Match on a Pivot table where some sub-columns have the same name (please see below)

	Path 1				Path 1 Total	Path 2
Row Labels	Jan	Feb	Mar	Apr		Jan	Feb
Client 1	12	155	230	25	422	132	169

I am trying to get the values for instance for Jan in Path 2 (ie. 132). If i am hovering over the cell, the column name appears as "Path 2 - Jan", so I have used the below formula (with a list of clients in "Pivot 1" and the sheet where I am entering the formula in "Aggregate"
=INDEX('Pivot 1'!$1:$1048,MATCH(Aggregate!$A6,'Pivot 1'!$A:$A,0),MATCH("Path 2 - Jan",'Pivot 1'!$4:$4,0))

I have decomposed the formula and the last part is the one that is not working ie. MATCH("Path 2 - Jan",'Pivot 1'!$4:$4,0). It's clearly the name of the sub-column that is not recognized.
Would anyone know what to do to fix this?
Many thanks

 

[Alex Blakenburg]

Is there any reason you don't want to use the GetPivot formulas ?

20210525 Pivot GetPivot.xlsx
	A	B	C	D
1
2
3
4
5	Client	Jan	Feb
6	Client 1	132	169
7	Client 2	0	700
8
Aggregate

Cell Formulas
Range	Formula
B6:C7	B6	=IFERROR(GETPIVOTDATA("Amt",'Data and Pivot'!$F$17,"Client",$A6,"Path","Path 2","Month",B$5),0)

 

[stefanh]

Thanks I never used this one before, that works perfectly

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.