INDEX Match Duplicate names displayed problem.

**Andrewman36** · 01-21-2014, 03:33 AM

Hello,

I currently have an excell sheet that matches the amount of sales individuals get and i have them ranked by which person has the most sales. However, when somebody has the same amount of sales, the value shows in the ranked column but only the first person listed shows up and duplicates. my current formula is =INDEX($A$36:$A$883,MATCH(G6,$R$36:$R$883,0)). attached is an an example of what i am trying to do, and will be a much easier for one to see then for me to explain. Thank you for any help!

-Andrew

guild.xlsx

**benishiryo** · 01-21-2014, 03:46 AM

welcome to the forum, Andrew. the formula is made much more complicated because merged cells cannot have array formulas. try to avoid merged cells in excel. you can combine C6:F6 by unmerging, format cells (while C6:F6 still selected) -> Alignment -> Horizontal -> Center Across Selection.

then you can use:
=INDEX($A$36:$A$883,SMALL(IF($R$36:$R$883=G6,ROW($R$36:$R$883)),COUNTIF(G$6:G6,G6))-ROW($R$36)+1)
...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.

if you insist on not changing, this regular formula would work too. more complicated & calculates at a slower speed though:

Formula:

Please Login or Register  to view this content.

**sktneer** · 01-21-2014, 03:55 AM

Another way to achieve this is using the following Array Formula which needs to be confirmed with Ctrl+Shift+Enter

Please Login or Register  to view this content.

**Andrewman36** · 02-05-2014, 11:00 PM

the original formula is all i can use because i have merged cells. that formula worked well, however, it will only rank people in that specific column, when i use the formula in the next 15 ranked to the column to the right it starts over. i attached an example of what i am talking about. Any ideas?