# EXCEL Pivot Table Question

**URL:** <https://boards.straightdope.com/t/excel-pivot-table-question/126836>\
**Category:** Factual Questions\
**Created:** [September 3, 2002, 5:49pm UTC](https://boards.straightdope.com/t/excel-pivot-table-question/126836 "2002-09-03T17:49:45Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tretiak](https://avatars.discourse-cdn.com/v4/letter/t/a88e57/32.png) [@Tretiak](https://boards.straightdope.com/u/Tretiak)\
**Post date:** [September 3, 2002, 5:49pm UTC](https://boards.straightdope.com/t/excel-pivot-table-question/126836/1 "2002-09-03T17:49:45Z")

</div>

I have a spreadsheet. Year’s worth of data…  
Column A: Date  
Column B: Month  
Column C: CData  
Column D: DData  
Column E: CData x DData = EData

I want to find the max of EDAta for each month. Not a problem, just run a pivot table. But I also need the corresponding CData and DData. Is there any way to do this with a pivot table or will I have to write a macro?

(This is a simplified version of the problem, the actual case is much more complicated)

---

<div class="post-metadata">

**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\
**Post date:** [September 3, 2002, 9:45pm UTC](https://boards.straightdope.com/t/excel-pivot-table-question/126836/2 "2002-09-03T21:45:58Z")

</div>

You could put columns next to the pivot table you made which run a Index(range, Match(pivot table data), column) calculation. This would effectively look up the values by matching the month and max(EData) column. This would run into trouble if there were more than one row that had the max value for EData … but it’s not clear how you’d handle that anyway. (Eg. Max(EData) is 60; do you pick CData=6 and DData=10, or another row where CData = 5 and DData=12?) It’s also harder when you’re matching more than one column, but you can work around that easily enough if your raw data is in date order … I hope this gives you a direction to look in, it’s hard to be more specific without more details on your problem.

---

<div class="post-metadata">

**Author:** ![Tretiak](https://avatars.discourse-cdn.com/v4/letter/t/a88e57/32.png) [@Tretiak](https://boards.straightdope.com/u/Tretiak)\
**Post date:** [September 4, 2002, 4:18am UTC](https://boards.straightdope.com/t/excel-pivot-table-question/126836/3 "2002-09-04T04:18:02Z")

</div>

Thanks, I think that should do the trick, actually.

---

<div class="post-metadata">

**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\
**Post date:** [September 4, 2002, 2:12pm UTC](https://boards.straightdope.com/t/excel-pivot-table-question/126836/4 "2002-09-04T14:12:42Z")

</div>

Glad I could help!