Ranking with Index Match Functions

[mellownando]

'm looking for some guidance on creating this lookup in excel.

i have a table similar to below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]STATE[/TD]
[TD]SalesPerson[/TD]
[TD]Count[/TD]
[TD]Percentage[/TD]
[TD][/TD]
[TD]RANK[/TD]
[TD]MA[/TD]
[TD]TX[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]MA[/TD]
[TD]AA[/TD]
[TD]68[/TD]
[TD]99.4%[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]CC[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]MA[/TD]
[TD]BB[/TD]
[TD]393[/TD]
[TD]97.5%[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]AA[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]MA[/TD]
[TD]CC[/TD]
[TD]566[/TD]
[TD]100.0%[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]BB[/TD]
[TD]EE[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]TX[/TD]
[TD]BB[/TD]
[TD]319[/TD]
[TD]0.0%[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]-[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]TX[/TD]
[TD]CC[/TD]
[TD]785[/TD]
[TD]22.3%[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]TX[/TD]
[TD]DD[/TD]
[TD]28[/TD]
[TD]88.7%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]TX[/TD]
[TD]EE[/TD]
[TD]664[/TD]
[TD]0.0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am looking to have the output in cells G2:H6 return the salesperson based on the state and percentage, and if the percentage points are the same, then use the count as rank, giving the output above. I've read through some processes on using rank index match, but i couldn't figure out how to rank using secondary source when the first rank is tied.

Help!

 

[mellownando]

My apologies, but the TX column should actually rank as below:

1.DD
2.CC
3.BB
4.EE
5.-

If the percentage is tied, i would like to have the salesperson with the lowest count ranked higher than the person with the highest count.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

STATE​

[/td][td]

SalesPerson​

[/td][td]

Count​

[/td][td]

Percentage​

[/td][td]

Helper​

[/td][td][/td][td]

RANK​

[/td][td]

MA​

[/td][td]

TX​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

MA​

[/td][td]

AA​

[/td][td]

68​

[/td][td]

99,4%​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td][/td][td]

1​

[/td][td]

CC​

[/td][td]

DD​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

MA​

[/td][td]

BB​

[/td][td]

393​

[/td][td]

97,5%​

[/td][td="bgcolor:#D9D9D9"]

3​

[/td][td][/td][td]

2​

[/td][td]

AA​

[/td][td]

CC​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

MA​

[/td][td]

CC​

[/td][td]

566​

[/td][td]

100,0%​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td][/td][td]

3​

[/td][td]

BB​

[/td][td]

BB​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

TX​

[/td][td]

BB​

[/td][td]

319​

[/td][td]

0,0%​

[/td][td="bgcolor:#D9D9D9"]

3​

[/td][td][/td][td]

4​

[/td][td]

-​

[/td][td]

EE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

TX​

[/td][td]

CC​

[/td][td]

785​

[/td][td]

22,3%​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td][/td][td]

5​

[/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

TX​

[/td][td]

DD​

[/td][td]

28​

[/td][td]

88,7%​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

TX​

[/td][td]

EE​

[/td][td]

664​

[/td][td]

0,0%​

[/td][td="bgcolor:#D9D9D9"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Helper column (gray area)
Formula in E2 copied down
=COUNTIFS(A$2:A$8,A2,D$2:D$8,">"&D2)+COUNTIFS(A$2:A$8,A2,D$2:D$8,D2,C$2:C$8,"<"&C2)+1

Formula in H2 copied across and down
=IFERROR(INDEX($B$2:$B$8,MATCH($G2,INDEX(($A$2:$A$8=H$1)*$E$2:$E$8,),0)),"-")

Hope this helps

M.