Count maximum occurences and find the corresponding value in excel

Question

I am struggling with a question in excel. Below is the sample data. I need to find the driver's name and his employee id who delivered maximum items. I am not sure how to use Index Match / Count / MAX in this case:

Emp ID    Driver Name   Item
1            Peter      TV
2            John       Microwave
3            Carl       Washing Machine
1            Peter      TV
1            Peter      Microwave

By looking at the sample, I know the answer is Peter, but how to find this value using formula.

I also need to find how many Drivers delivered most items. In this scenario, the answer is 1 (Peter) Trying to use countif here, didn't work.

Answer 1

@BigBen's solution didn't work for me because MODE.SNGL seems to be a child of Excel 365 which I don't have. If you are in the same situation, the following will work on earlier versions of Excel.

Extract the name with [G2] =INDEX(Names,MODE(MATCH(Names,Names,0))) and the Employee ID with

=INDEX(EmpID,MATCH(G2,Names,0))

Replace "G2" in this formula with the first formula if you don't want to refer to the name extracted by it.

Both formulas are written using named ranges for the columns of Employee IDs and Drive Names. This is to make them easier to read and edit. It also makes them easier to manage because you can define the named range dynamically.

Answer 2

To get the Employee ID:

=MODE.SNGL(A2:A6)

To get the name:

=INDEX(B2:B6,MATCH(MODE.SNGL(A2:A6),A2:A6,0))

In case there is a "tie", use MODE.MULT instead of MODE.SNGL. Depending on your version of Excel, you may have to enter the formula with Ctrl+Shift+Enter.