# Excel - How to return different match values with other formulas?

I have the following table and resulting formulas: 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.

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.

• Thanks, that works great. The genius part was the COUNTIFS on the incrementing range. Aug 17 '18 at 15:35
• 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. 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 /(-_-)\ 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. ( : Aug 17 '18 at 16:36

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)
``````
• 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. Aug 17 '18 at 16:29
• I've tested it with 3 numbers @ tie.. and I got the same results as Scott. ( : Aug 17 '18 at 16:33