Does it not work at all, or does it show the same Product ID for two equal
revenues?

I played around with some numbers and the highest value always works (in the
simplified example anyway), but the second and third ID's are the same as the
first if the revenues are equal. Is this what going on? Or is it always
wrong?

The problem is LARGE only finds the value not the position, and MATCH finds
the position of the first one only. There is a way around it though. If the
values are equal you can change the list to not include anything before the
first value. So you would MATCH the first value and use that number to start
the begining of the new list to MATCH. So you would have something like this.
=INDIRECT(~~MATCH(~~INDIRECT(~~MATCH)))
Give me some time to work on it.

"Roddd" wrote:

Thanks Sloth.

Your formula for finding the Top 3 largest revenue amounts per Cust ID works
fine.

However, finding the same Prod ID label associated w/ each of the Top 3
revenue amounts per Cust ID does not. It works in this simplified example,
but not in a dataset of 40,000+ rows (and I think you allude to this in your
reply).

Somehow, I think I need a formula that finds the row where the Top N revenue
amount and correct Cust ID occurs (using LARGE function from column C), and
then gets the corresponding Product ID related to the Row# that was captured.

Thanks again for your efforts,

"Sloth" wrote:

This is the closest I can get. The only restriction is the revenue must be
unique for that id number. I can't figure a way around it because the match
finds the first value in a list, and lookup functions need to be sorted.
With that being said here is my solution.

I used Book1 and Book2. It keeps the formulas from getting to big.
Customer ID's are numbers with a custom number format of
000000
ProductID's are text format as text

In C2 of Book1 use this formula to get the largest revenue for the ID number
=LARGE(([Book2.xls]Sheet1!$A$2:$A$7=$A2)*([Book2.xls]Sheet1!$C$2:$C$7),1)
In B2 of Book1 use this formula to get the matching product ID number for
the revenue
=IF(C2<0,INDIRECT("Sheet2!B"&1+MATCH(C2,([Book2.xls]Sheet1!$A$2:$A$7=$A2)*([Book2.xls]Sheet1!$C$2:$C$7),0)),"")
Both formulas are array formulas, which means you have to enter them by
pressing ctrl+shift+enter

Then, copy across but change E2 and G2 to look like this (respectively)
=LARGE(([Book2.xls]Sheet1!$A$2:$A$7=$A2)*([Book2.xls]Sheet1!$C$2:$C$7),2)
=LARGE(([Book2.xls]Sheet1!$A$2:$A$7=$A2)*([Book2.xls]Sheet1!$C$2:$C$7),3)

Then, copy down and you should get the aproppiate results. Hope this helps,
and sorry I couldn't get a perfect solution.

"Roddd" wrote:

In workbook A, I have a unique list of customer IDs in column A:
Cust ID
1 000011
2 000012
3 000013
4 000014
5 000015

In workbook B, I have a bunch of product / revenue information related to
each customer. Each customer may have multiple rows of data on this
workbook, but each row would be a unique Cust & Prod ID combination:
Cust ID Prod ID Revenue
1 000013 ABC123 1,000
2 000011 ABC987 10,000
3 000014 ABC234 5,000
4 000014 ABC345 15,000
5 000011 ABC789 9,000
6 000014 ABC567 20,000

Back in Workbook A, I would like to populate columns B - G with the Top 3
revenue generating products and their associated revenue:

Cust ID TProd1 TRev1 TProd2 TRev2 TProd3 TRev3
1 000011 ABC987 10,000 ABC789 9,000
2 000012
3 000013 ABC123 1,000
4 000014 ABC567 20,000 ABC345 15,000 ABC234 5,000
5 000015

Any thoughts on getting this started? Thanks.