Vlookup, Max, SumProduct...HELP with formula!

[ellyndee]

Here is my dilemma. I have a pivot table which has a customer number in column A, customer group in column B, and the amount spent per customer per group in column C. On my nonpivot worksheet, I want to look up the customer number from the pivot table and pull the value from the customer group column but based on the max value of the amount groupings for the customer number.

Example
Customer Number, Customer Group, Amount
1,145,5000
1,125,1000
1,100,6000

So for customer number 1, the result would pull in 100 because it is associated with the highest amount among the group numbers. Does this make sense? PLEASE HELP! I have tried to figure this out for two days. I can't get it. I am also open to changing pivot table into text and copying to non-pivot worksheet for ease of calculation.

[Martijnvc]

Problem with VLOOKUP in this setup is that it keeps finding the first entry, and then stops.

I'd suggest two options (both manipulating your pivot table) to achieve what you want:
1. Sort the customer group based on the amount (high to low). This way VLOOKUP always finds the highest group for a customer and returns that row.
2. Similar to 1: filter the top 1 of a customer group based on amount

Will this help you on your way?

[Tony Valko]

Try this...

With your data in the range A2:C4.

This array formula**:

=INDEX(B2:B4,MATCH(1,IF(A2:A4=1,IF(C2:C4=MAX(IF(A2:A4=1,C2:C4)),1)),0))


** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[ellyndee]

I never thought of that. MANY THANKS!

[Tony Valko]

I never thought of that. MANY THANKS!

Can't tell who you're replying to.