Using Index Match to Pivot table

[nazarene]

Dear Sirs,

I am very stressful how to do the link from Pivot table using index match, as I always get the error value.
What I need to do is to link to pivot table using index match matching the percentage based on the Cost Center, Job and the months.
I don't know what is the problem, it always comes to error my formula INDEX('%'!D$5:M$55,MATCH('Jul21'!U$11,'%'!A$5:A$55,0),MATCH('Jul21'!AG9,'%'!B$5:B$55,0)*MATCH(AE$3,'%'!A$2:N$2,0))
Can please someone help me to resolve this. Thanks in advance

 

[Alex Blakenburg]

The way it looks to me, you should be using GetPivot formulas not Index/Match.
Any reason you are not using the GetPivot method ?

 

[nazarene]

Yes sir, actually i dont know how to use GetPivot method, can you show me sir how to use that Getpivot on my table? as i dont know how to start

 

[Alex Blakenburg]

Can you give me an XL2BB of a few lines of your summary sheet and also of your pivot table.

 

[nazarene]

Sir, how can I upload the file ?

 

[Alex Blakenburg]

Ideally use XL2BB, see details below.
If you can't do that then load the spreadsheet to a shared platform such as google drive, onedrive, dropbox etc. Make it available to anyone with the link and post the link here.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[nazarene]

I use the we transfer

Data.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

wetransfer.com

 

[Alex Blakenburg]

Per your original post the formula is going to need the month number.
I have put it in: AE4
So you can see how it works.

Then in the Cell AE11 put this:-

=GETPIVOTDATA("%",'%'!$A$3,"Cost Ctr",$U11,"Job",AE$9,"Per",TEXT($AE$4,"000"))

Data.xlsx
	AE
1
2
3	Month
4	7
5
Jul21

 

[nazarene]

Dear Sir Alex,
Thank you so much, your formula, works perfectly sir.

Thank you again

 

[Alex Blakenburg]

Thanks for letting know. Glad I could help.