2

I have the following table and resulting formulas: enter image description here

First I have the sales person and the number of sales.

Then in columns D and E, I want to know who are the best salesepeople.

The formula in E2 is =LARGE($B$2:$B$11,$D2) and filled down.

Then I want to know the names of best salespeople. The formula in F2 is =INDEX($A$2:$A$11,MATCH($E2,$B$2:$B$11,0)) filled down.

The problem is that 25 and 15 each show up twice for Bob and Jeff and Sue and Carl. But in column F Bob and Sue show up twice because Match returns for the first match only. I need 25 to list Bob and then Jeff and 15 to list Sue and then Carl.

The way I was looking to do it is to check how many times the the value in E appear. If it appears multiple times, then find which instance of that value the cell is next to and then find that value from the Match of the name. So for 25 with 2 values F4 is by the first 25 in E so it would return Bob, and F5 which is by the second 25 would return Jeff.

Sounds simple, but I have not been able to put it in a working formula. I am trying to have a single formula in F giving the desired results.

2

Use AGGREGATE as a SMALL function to return the correct row to INDEX:

=INDEX(A:A,AGGREGATE(15,6,ROW($B$2:$B$11)/($B$2:$B$11=$E2),COUNTIFS($E$2:$E2,$E2)))

The COUNTIFS($E$2:$E2,$E2) will return 1 for the first and 2 for the second instances of the number, thus forcing the AGGREGATE to return the first then the second name.

5
  • Thanks, that works great. The genius part was the COUNTIFS on the incrementing range.
    – MichaelE
    Aug 17 '18 at 15:35
  • 1
    Also, Aggregate only works with Excel 2010 and later for compatibility with older version I used: =INDEX($A$2:$A$11,SMALL(IF(E2=$B$2:$B$11,ROW($B$2:$B$11)-ROW(INDEX($B$2:$B$11,1,1))+1),SUM(IF(E2=$E$2:$E2,1,0)))) but it needs Ctrl-Shift-Enter. Sum is instead of the count, and the small finds that instance of the value.
    – MichaelE
    Aug 17 '18 at 15:38
  • If @Scott don't mind.. I just don't get how ROW($B$2:$B$11)/($B$2:$B$11=$E2) become a range for the PERCENTILE.INC() function /(-_-)\
    – p._phidot_
    Aug 17 '18 at 16:15
  • @p._phidot_ 15 is Small not Percentile. It creates an array of numbers and errors(#DIV/0). The 6 ignores the errors. and the SMALL returns the correct number. Aug 17 '18 at 16:18
  • Thanks Scott.. still new to small() function manipulation.. I knew aggregate() is great for ignoring error.. but sometimes lost when it was extensively used for list manipulation. At least for this case I get the idea of how the list 'identify' &'sort' mechanism works here.. Thanks a bunch. Really appreciate it. ( :
    – p._phidot_
    Aug 17 '18 at 16:36
0

My small attempt :

=IF(F1<>F2,INDEX($B$2:$B$13,MATCH(F2,$C$2:$C$13,0)),INDEX(OFFSET($B$2:$B$13,MATCH(G1,$B$2:$B$13,0),0,ROWS($B$2:$B$13)),MATCH(F2,OFFSET($B$2:$B$13,MATCH(G1,$B$2:$B$13,0),1,ROWS($B$2:$B$13)),0)
2
  • Thank you, This works, but it is limited to only 2 of a certain value being the same, I think? Scott's answer works with any generic case.
    – MichaelE
    Aug 17 '18 at 16:29
  • I've tested it with 3 numbers @ tie.. and I got the same results as Scott. ( :
    – p._phidot_
    Aug 17 '18 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.