Using Index and Match Function on a Pivot Table

[hiker8117]

I need to index and match against 3 sets of criteria in a Pivot Table to pull into another workbook. It looks like I can only use 2 sets of criteria. I am trying to pull the numbers based on a Job Order "JO" number such as 828MS40650 against a code such as 003 to pull the 5457.99. But I need one more set of data to truly index and match. I need another set of data that is above the codes. I need to show this 3rd set of data that flows across just like the codes.

JO	001	002	003	004	005	006	007	008	009	010	011
828MR60770										15842.63​
828MS40605			5457.99​
828MS41100		131.15​						912.93​	1194.24​
828MS50495											21902.07​
828MS60100	3183.2​					9587.3​
828MS60400	397.9​
828MS60412	2985.13​
828MS60425			374.16​		2059.02​			8741.14​
828MS60426	2393.08​		977.45​			166.73​	660.09​	3476.22​

I need to create this pivot table to summarize the totals as the file is a many to many against a file that needs to pull in the numbers as a one to many.

Any ideas.

Appreciate any help.

Regards

Gene

 

[AlanSidman]

Pictures
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

 

[hiker8117]

Attached is a sample workbook. I need to pull in the Dollars from the Pivot that feeds off of Report1 (2) into the Total Dollars column in Report 1.
I need the Pivot to list 3 criteria that shows TSTSD, JO iand Code. With just the JO and Code in the Pivot I get duplicate Total Dollars highlighted in yellow. The yellow highlighted records have the same JO and Code but different TSTSD. Any help is appreciated. Report1 (2) is many to many records and I need to summarize the dollars via a pivot table.

Regards

Gene

 

[GraH - Guido]

Alternative is using GETPIVOTDATA function after adding the third variable in your pivot.
H2 =IFERROR(GETPIVOTDATA("LBR",Pivot!$A$1,"TSTSD",A2,"JO",B2,"Code",C2),"")
And drag down.

 

[hiker8117]

Thank you very much. It worked perfectly