Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Index match formula pulling data from pivot table

Posted on 2014-03-05
5
Medium Priority
?
4,791 Views
Last Modified: 2014-03-11
Hi Experts Excel 2007

I am using the following formula =IFERROR(INDEX(J:j,MATCH(B1,C:C,0)) +INDEX(J:j,MATCH(B1,D:D,0)) +INDEX(J:j,MATCH(B1,H:H,0)),"")

How every the formula sometime does not read the data from the pivot table if the order of the data changes....looking to make this dynamic
             01.01.2014.        02.01.2014.       03.01.2014.      04.01.2014.       05.01.2014
Labels
Abc
Def
Ghi
Jkl
0
Comment
Question by:route217
  • 2
  • 2
5 Comments
 

Author Comment

by:route217
ID: 39907728
Ok here's a more realistic index match formula...

=IFERROR(INDEX('Pivot '!$B$3:$AZ$14,MATCH($A$154,'Pivot '!$B$3:$B$14,0),MATCH(D152,'Pivot '!$B$2:$AZ$2))+
INDEX('Pivot '!$B$3:$AZ$14,MATCH($A$155,'Pivot '!$B$3:$B$14,0),MATCH(D152,'Pivot '!$B$2:$AZ$2))+ INDEX('Pivot '!$B$3:$AZ$14,MATCH($A$156,'Pivot '!$B$3:$B$14,0),MATCH(D152,'Pivot '!$B$2:$AZ$2)),"")
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39908578
Hi,

Not all your match functions have a matchtype 0, it assumes MatchType 1( thus the data being in ascending order)

Regards
0
 

Author Comment

by:route217
ID: 39908690
Thanks for the feedback
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 39910769
You can also use GETPIVOTDATA function.

To see the syntax, type = in a cell and then select a value from within the pivot. Press enter, the selection criteria for the pivot data will be hardcoded in the formula but can be changed to cell references containing the pivot field name and the pivot field value.

Thanks
Rob
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39910781
Or, extract the result you want from the source data rather than the pivot using SUMIFS function.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question