Analysis116
New Member
 Joined
 Nov 10, 2020
 Messages
 9
 Office Version

 365
 Platform

 Windows
I would like to find the maximum (largest) value in a range of cells and return all values for multiple occurrences/duplicate maximum (largest) values for each user in excel.
Below is a list of the count of trips made by each user ID (user 1, 2, 3, etc. ) to purchase each brand of chocolate.
For example for user 1, he has purchased both Hersheys and Reeses three times (duplicate maximum values). If the Xlookup function is used with the MAX function, then only the brand corresponding to the first MAX value will return, Hersheys, and not Reeses. For user 2 you can see that he has purchased Hersheys, Reeses and Snickers all once. Thus, there are also multiple maximum values in that case also. I would like to return all maximum values if there are multiple occurrences of the maximum value.
Separately, I would also like to return the value for the nth maximum value.
Here is an example data set:
Below is a list of the count of trips made by each user ID (user 1, 2, 3, etc. ) to purchase each brand of chocolate.
For example for user 1, he has purchased both Hersheys and Reeses three times (duplicate maximum values). If the Xlookup function is used with the MAX function, then only the brand corresponding to the first MAX value will return, Hersheys, and not Reeses. For user 2 you can see that he has purchased Hersheys, Reeses and Snickers all once. Thus, there are also multiple maximum values in that case also. I would like to return all maximum values if there are multiple occurrences of the maximum value.
Separately, I would also like to return the value for the nth maximum value.
Here is an example data set:
User ID  Hersheys  Reeses  Snickers  Twix  Candy Brand with the Highest Number of Trips for User 
1  3  3  0  0  
2  1  1  1  0  
3  4  4  1  1  
4  0  0  1  0  
5  2  1  2  0 