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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies