Home

Index and Match with a pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-388795%22%20slang%3D%22en-US%22%3EIndex%20and%20Match%20with%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388795%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20making%20our%20sales%20reports%20less%20manual%20entry%20and%20want%20to%20use%20formulas%20to%20grab%20from%20a%20pivot%20table%20that%20has%20all%20our%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20pivot%20table%20currently%20contains%20three%20of%20the%20seven%20data%20fields%20that%20I'm%20looking%20to%20get%20information%20from.%20As%20of%20right%20now%20the%20sales%20tracking%20spreadsheet%20has%20the%20reps%20name%20under%20column%20A.%20The%20formula%20has%20it%20starting%20at%20%24A524%20so%20when%20I%20drag%20and%20copy%20the%20formula%20it%20will%20just%20continue%20down%20to%20the%20next%20rep.%20When%20the%20formula%20is%20referencing%20the%20pivot%20table%2C%20in%20another%20spreadsheet%2C%20column%20A%20will%20always%20be%20absolute%20since%20it%20contains%20the%20reps%20names%20just%20like%20the%20sales%20tracking%20spreadsheet.%20The%20way%20I%20have%20the%20data%20set%20up%20for%20columns%20C-E%2C%20which%20contain%20the%20sales%20numbers%2C%20I%20only%20have%20the%20rows%20as%20absolute%20so%20when%20I%20drag%20and%20copy%20the%20column%20they%20will%20move%20to%20the%20next%20portion%20of%20data%20in%20the%20pivot%20table%20and%20pull%20that%20number%20into%20the%20sales%20tracking%20spreadsheet.%20This%20formula%20currently%20works%20for%20bringing%20back%20%220%22%20when%20the%20rep%20doesn't%20have%20production%20in%20the%20pivot%20table%20but%20I%20get%20%23VALUE!%20errors%20in%20the%20sales%20tracking%20spreadsheet%20when%20the%20sales%20reps%20name%20isn't%20in%20the%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DINDEX('Pivot_table'!C%244%3AC%2429%2CIFNA(MATCH(%24A524%2C'Pivot_table'!%24A%244%3A%24A%2429%2C0)%2C%220%22))%3C%2FPRE%3E%3CP%3E%26nbsp%3BIndex(looking%20at%20the%20pivot%20table%20for%20the%20sales%20numbers%2C%20IFNA(MATCH(is%20looking%20for%20the%20rep%20name%2C%20in%20the%20first%20column%20of%20the%20pivot%20table%20for%20a%20match%2C0)%2C%22I'm%20not%20sure%20if%20this%20IFNA%20is%20working%20for%20what%20I%20need%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-388795%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389451%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20with%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20I'll%20give%20that%20a%20try%20today!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388945%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20with%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F308290%22%20target%3D%22_blank%22%3E%40alechanson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20suggest%20that%20you%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-gb%2Farticle%2Fgetpivotdata-function-8c083b99-a922-4ca0-af5e-3af55960761f%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EGETPIVOTDATA()%3C%2FA%3E.%20It's%20a%20lookup%20function%20designed%20for%20pivot%20tables.%3C%2FP%3E%3CP%3EAnd%20IFNA()%20should%20be%20the%20outermost%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
alechanson
New Contributor

Hi,

 

I am working on making our sales reports less manual entry and want to use formulas to grab from a pivot table that has all our data.

 

The pivot table currently contains three of the seven data fields that I'm looking to get information from. As of right now the sales tracking spreadsheet has the reps name under column A. The formula has it starting at $A524 so when I drag and copy the formula it will just continue down to the next rep. When the formula is referencing the pivot table, in another spreadsheet, column A will always be absolute since it contains the reps names just like the sales tracking spreadsheet. The way I have the data set up for columns C-E, which contain the sales numbers, I only have the rows as absolute so when I drag and copy the column they will move to the next portion of data in the pivot table and pull that number into the sales tracking spreadsheet. This formula currently works for bringing back "0" when the rep doesn't have production in the pivot table but I get #VALUE! errors in the sales tracking spreadsheet when the sales reps name isn't in the pivot table.

 

 

=INDEX('Pivot_table'!C$4:C$29,IFNA(MATCH($A524,'Pivot_table'!$A$4:$A$29,0),"0"))

 Index(looking at the pivot table for the sales numbers, IFNA(MATCH(is looking for the rep name, in the first column of the pivot table for a match,0),"I'm not sure if this IFNA is working for what I need"))

 

Thanks for any help!

2 Replies

@alechanson 

I would suggest that you use GETPIVOTDATA(). It's a lookup function designed for pivot tables.

And IFNA() should be the outermost function.

 

@Detlef Lewin 

Thanks, I'll give that a try today!

Related Conversations
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies