Home

Lookup in Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-320245%22%20slang%3D%22en-US%22%3ELookup%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320245%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20lookup%20the%20max%20sales%20for%20a%20month%20by%20employee.%20The%20Pivot%20Table%20shows%20the%20layout.%20I%20know%20i%20could%20just%20use%20a%20max%20function%20to%20return%20each%20number%2C%20but%20I%20want%20a%20formula%20that%20will%20lookup%20an%20employee's%20name%20and%20return%20its%20max%20value.%20Additionally%2C%20I%20want%20to%20lookup%20the%20calculated%20max%20value%20and%20return%20its%20corresponding%20product%20ID.%20Any%20ideas%20for%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-320245%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-320328%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320328%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Luck%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERemove%20all%20fields%20from%20PivotTable%20areas%2C%20then%20drag%20the%20Name%20field%20to%20Rows%20area%2C%20and%20drag%20the%20SoldFor%20field%20to%20Values%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20will%20give%20you%20each%20employee%20and%20the%20sum%20of%20his%20sales.%3C%2FP%3E%3CP%3EThe%20sum%20is%20the%20default%20function%20here%2C%20but%20you%20can%20change%20it%20to%20Max.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20so%2C%20move%20the%20cursor%20to%20any%20cell%20in%20the%20%3CSTRONG%3ESum%20of%20SoldFor%3C%2FSTRONG%3E%20field%2C%20right-click%2C%20and%20select%20%3CSTRONG%3ESummarize%20Values%20By%3C%2FSTRONG%3E%20%26gt%3B%26gt%3B%20%3CSTRONG%3EMax%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20that%2C%20you%20can%20this%20formula%20to%20get%20the%20ID%20for%20each%20Max%20value.%3C%2FP%3E%3CPRE%3E%3DINDEX(Table1%5BID%5D%2CMATCH(F2%2CTable1%5BSales%5D%2C0))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68935i1A077A4FEF2ABDCC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Summarize%20Values%20By%20Max.png%22%20title%3D%22Summarize%20Values%20By%20Max.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
LuckB10
Occasional Visitor

I'm trying to lookup the max sales for a month by employee. The Pivot Table shows the layout. I know i could just use a max function to return each number, but I want a formula that will lookup an employee's name and return its max value. Additionally, I want to lookup the calculated max value and return its corresponding product ID. Any ideas for me?

1 Reply

Hi Luck,

 

Remove all fields from PivotTable areas, then drag the Name field to Rows area, and drag the SoldFor field to Values area.

 

This is will give you each employee and the sum of his sales.

The sum is the default function here, but you can change it to Max.

 

To do so, move the cursor to any cell in the Sum of SoldFor field, right-click, and select Summarize Values By >> Max.

 

After that, you can this formula to get the ID for each Max value.

=INDEX(Table1[ID],MATCH(F2,Table1[Sales],0))

Summarize Values By Max.png

 

 

Hope that helps

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
Copy cell value from different table row
Edgar Soares in Excel on
2 Replies