INDEX MATCH & LARGE Functions, Multiple Criteria

[skyhawk3485]

OK all you Excel experts, I apologize in advance as I know absolutely nothing about Excel formulas, except how to search for answers on this forum and try to do something similar

I am developing a report that is basically a sales leaderboard, and I have two issues:

1) I have a Top 5 count for the entire company, and it works OK, unless two or more people are tied (this happens quite often....). Here is the formula I used:

=INDEX($B$3:$B$30,MATCH(LARGE($C$3:$C$30,E4),$C$3:$C$30,0),1)

Context:
Column B: Salesperson Name (which is what I want to return)
Column C: Units Sold
(skipped column D for formatting)
Column E: Use it for the ranking (E4 is 1, E5 is 2, etc.)

How can I show any ties and list all the names of those people?

2) I would like to create a single cell that shows the top rep by region, and the corresponding region is in Column A. I only need the Top 1 person (B) who is within the region (A) and has the highest results (C). Tried multiple searches on the site by adding an IF function, etc. and I frankly have no clue what I'm doing.

Any help is greatly appreciated! Thank you so much!

[Tony Valko]

See if this helps:

https://www.excelforum.com/showthread.php?p=3396886

[Sam Capricci]

Are you focusing on one column or multiple columns for the rankings? Are you using the rank formula?
This little formula I use often to break ties...
=rank(E2,$E$2:$E$100,0)+countif($E$2:E2,E2)-1 and drag down (assuming your ranked values are in column E starting in E2.

Edited: pointed to correct column.

[skyhawk3485]

Thank you - I modified the sheet to include the RANK formula, that should suffice

Still needing help with the other piece though... Just looking to have a single cell that returns the name of the highest ranked rep by region?

You guys are awesome BTW

[azumi]

Try post small example data, click "Go Advanced" button and find paperclip image button to attach your file....

[FDibbins]

Does your original formula not give yoyu that?

=INDEX($B$3:$B$30,MATCH(LARGE($C$3:$C$30,1),$C$3:$C$30,0))
or maybe...
=INDEX($B$3:$B$30,MATCH(max($C$3:$C$30),$C$3:$C$30,0))

[skyhawk3485]

It gives me the highest overall value, but I'm trying to return the highest rep by region... for example, I can't figure out how to filter out just the reps in the North region and show me who is ranked the highest. It seems so simple, I must be overthinking it...

I'm going to try and attach a copy of the file as I have it now, maybe that will help with context:

Sales Board.xlsx

[FDibbins]

1st, instead of Top North etc, just use North.

Then use this ARRAY formula for eact section...
=INDEX($C$4:$C$13,MATCH(F12&LARGE(IF($B$4:$B$13=F12,$D$4:$D$13),1),INDEX($B$4:$B$13&$D$4:$D$13,0),0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Tony Valko]

Try these array formulas**:

North:

=INDEX(C4:C13,MATCH(MAX(IF(B4:B13="North",D4:D13)),IF(B4:B13="North",D4:D13),0))

South:

=INDEX(C4:C13,MATCH(MAX(IF(B4:B13="South",D4:D13)),IF(B4:B13="South",D4:D13),0))

East:

=INDEX(C4:C13,MATCH(MAX(IF(B4:B13="East",D4:D13)),IF(B4:B13="East",D4:D13),0))

West:

=INDEX(C4:C13,MATCH(MAX(IF(B4:B13="West",D4:D13)),IF(B4:B13="West",D4:D13),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[skyhawk3485]

Beautiful! You guys rock, THANK YOU!

[FDibbins]

Happy to help, thanks for the feedback

[Tony Valko]

You're welcome. We appreciate the feedback!

[DasPXL]

Hi all,

I know this is an old thread but it looks to be the closest thing to a problem I am facing and I've been trying to figure it out for hours on end.

My goal is to accomplish essentially the same task but instead of only retrieving the name of the #1 (Max) salesperson based on region, I want to return the Top 5 based on region. I would ideally like to accomplish this without the use of a helper column and have it all contained within one cell. I don't need to return the amounts, just the names associated with the Top amounts. Most of the other solutions I've seen typically first retrieve the amount, and then do an index match off of the amount utilizing a Small function in order to get to the name.

Any help would be greatly appreciated!

Thanks,
Pxl

[azumi]

See post #5

[FDibbins]

DasPXL welcome to the forum

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[DasPXL]

Thanks FDibbins, apologies.

I will post a new thread, thanks for pointing me in the right direction.

Thanks,
Pxl