Is there a way to use the lookup function to retrieve ranked data based on multiple criteria?
For example, I am trying to lookup the name of a customer that has the largest volume of sales in a specific month,region, and type of business.
I have tried using a pivot table, but this only returns the region with the greatest amount of sales, not the customer with that region and since there are many regions I do not want to create a specific pivot table for each region.
The formula I am looking for would be something like:
'=INDEX(DATA!$A:$BF,MATCH(LARGE(DATA!$Z:$Z,1),IF(DATA!$H:$H=Region!$C$2,IF(DATA!$F:$F=Region!$C$4,IF(DATA!$M:$M="201101",))),0),5)
If all of my data is located on the DATA tab:
Z = Sales
H = Region
F = Type of business
M = Month
E = Customer Name
Please let me know if this is possible.
Thanks!
For example, I am trying to lookup the name of a customer that has the largest volume of sales in a specific month,region, and type of business.
I have tried using a pivot table, but this only returns the region with the greatest amount of sales, not the customer with that region and since there are many regions I do not want to create a specific pivot table for each region.
The formula I am looking for would be something like:
'=INDEX(DATA!$A:$BF,MATCH(LARGE(DATA!$Z:$Z,1),IF(DATA!$H:$H=Region!$C$2,IF(DATA!$F:$F=Region!$C$4,IF(DATA!$M:$M="201101",))),0),5)
If all of my data is located on the DATA tab:
Z = Sales
H = Region
F = Type of business
M = Month
E = Customer Name
Please let me know if this is possible.
Thanks!
