Index and Match

[m3ko]

Hi everyone,

Could I get some help with my formulae.
Below is my pivot table I created from a data source.
I defined this as PivotTableRange under sheet called Data - Pivot using
=OFFSET('Data - Pivot'!$A$1, 0, 0, COUNTA('Data - Pivot'!$A:$A), COUNTA('Data - Pivot'!$1:$1))

Sales history type.xlsx
	A	B																								Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Sum of TrnQty
2	StockCode	MovementType																								2018	2019	2020	2021	2022	2023	2024	2025	Grand Total
3	10101a	S																														1		1
4	41016.5	I																											3.5	48.47	60	50.59	10.01	172.57
5	41016.5	S																													1.46			1.46
Data - Pivot

On my main usage sheet called "Summary", I want to look up items and have it return the Grand Total.
But, it must meet 2 criteria since each item can either be in Movement type "S" or "I" as shown in my PivotTableRange (on sheet named Data - Pivot).
The problem I am having is that as I refresh my pivot table with new data, I expect the columns to change so the Grand Total from the Pivot Table will be dynamic.
The start year is blank for some of the items, so I think that is messing with my formula as it is returning values before the next blank column, which in this case is one of the lookup criteria of "S".

=INDEX(PivotTableRange, MATCH(1, (INDEX(PivotTableRange, 0, 1)=B4)*(INDEX(PivotTableRange, 0, 2)="S"), 0), COUNTA('Data - Pivot'!1:1))

Sales history type.xlsx
	B	C	D	E	F
2	Manual Lookup:
3	Stock Code	Product Class	Qty On Hand	Test	Ytd Sold
4	41016.5	30SP	0	S	50.59
Summary

Cell Formulas
Range	Formula
C4	C4	=IFERROR(VLOOKUP($B$4,StkInfo,2,FALSE),"")
D4	D4	=IFERROR(VLOOKUP($B$4,StkInfo,4,FALSE),"")
E4	E4	=INDEX(PivotTableRange, MATCH(1, (INDEX(PivotTableRange, 0, 1)=B4)*(INDEX(PivotTableRange, 0, 2)="S"), 0), COUNTA('Data - Pivot'!1:1))
F4	F4	=IFERROR(VLOOKUP($B$4,StkInfo,5,FALSE),"")

Named Ranges
Name	Refers To	Cells
PivotTableRange	=OFFSET('Data - Pivot'!$A$1, 0, 0, COUNTA('Data - Pivot'!$A:$A), COUNTA('Data - Pivot'!$1:$1))	E4

Could I get some assistance or directed to a solved thread please!
Thanks everyone!

 

[Peter_SSs]

I defined this as PivotTableRange under sheet called Data - Pivot using
=OFFSET('Data - Pivot'!$A$1, 0, 0, COUNTA('Data - Pivot'!$A:$A), COUNTA('Data - Pivot'!$1:$1))

Wouldn't you have used row 2?

.. and the same in your formula?
=INDEX(PivotTableRange, MATCH(1, (INDEX(PivotTableRange, 0, 1)=B4)*(INDEX(PivotTableRange, 0, 2)="S"), 0), COUNTA('Data - Pivot'!2:2))
instead of
=INDEX(PivotTableRange, MATCH(1, (INDEX(PivotTableRange, 0, 1)=B4)*(INDEX(PivotTableRange, 0, 2)="S"), 0), COUNTA('Data - Pivot'!1:1))

Also, do you need to go to the trouble of defining the named range? You could use something like this instead. Just make the ranges plenty big enough to accommodate any future Pivot Table expansion.
=INDEX('Data - Pivot'!A:ZZ, MATCH(1, ('Data - Pivot'!A1:A1000=B4)*('Data - Pivot'!B1:B1000="S"), 0), COUNTA('Data - Pivot'!2:2))

 

[m3ko]

Wouldn't you have used row 2?

.. and the same in your formula?
=INDEX(PivotTableRange, MATCH(1, (INDEX(PivotTableRange, 0, 1)=B4)*(INDEX(PivotTableRange, 0, 2)="S"), 0), COUNTA('Data - Pivot'!2:2))
instead of
=INDEX(PivotTableRange, MATCH(1, (INDEX(PivotTableRange, 0, 1)=B4)*(INDEX(PivotTableRange, 0, 2)="S"), 0), COUNTA('Data - Pivot'!1:1))

Also, do you need to go to the trouble of defining the named range? You could use something like this instead. Just make the ranges plenty big enough to accommodate any future Pivot Table expansion.
=INDEX('Data - Pivot'!A:ZZ, MATCH(1, ('Data - Pivot'!A1:A1000=B4)*('Data - Pivot'!B1:B1000="S"), 0), COUNTA('Data - Pivot'!2:2))

OMG thanks so much Peter, I'm such an idiot.
This started out as something different and I later went back to add in to add a second condition (MovementType).
Did not update it to use row 2 instead on the named range and everything.
Hopefully I can get better and be able to offer solutions too one day.
Is there a book or something out there that you used before XD?

Orz.

Thanks for the solution, cheers!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.