Ranking with duplicates

[michael.collins22]

I have an array of data, produced by formulas, that ranks NFL teams and over/under calls.

K L M N
BUF 7 Over1 25
DET 8 Over2 25
BAL 11 Under3 25
CLE 15 Over4 24
CHI 2 Over5 9
JAX 11 Over6 29
SEA 1 Over7 15
ARI 30 Over8 31
CAR 21 Over9 4
TB 21 Over10 25
SF 6 Over11 13
HOU 3 Over12 18
CIN 10 Over13 18
SD 18 Under14 21
PHI 5 Under15 14
STL 17 Under16 31

I want to keep the array, but add a vertical list next to it with the rankings reshuffled while including duplicates to look something like this.

Q R
1 SEA
2 CHI
3 HOU
4 Over9
5 PHI
6 SF
7 BUF
8 DET
9 Over5
10 CIN
11 BAL
11 JAX
13 Over11
14 Under15
15 CLE
15 Over7
17 STL
18 SD
18 Over12
28 Over13
21 CAR
21 TB
21 Under14
24 Over4
25 Over1
25 Over2
25 Under3
25 Over10
29 Over6
30 ARI
31 Over8
31 Under16

Currently, I have to produce the vertical list manually. I came up with a formula that will look up and rank the teams and calls, but it ignores duplicates. I type in numbers in column Q and have the following formula in column R. I'd rather have a formula(s) that will lookup and rank the teams and calls including duplicates. Any suggestions?

=IF(ISNUMBER(MATCH($Q1,$L$1:$L$16,0)),HLOOKUP($K$1,$K$1:$K$16,MATCH($Q1,$L$1:$L$16,0)),IF(ISNUMBER(MATCH($Q1,$N$1:$N$16,0)),HLOOKUP($M$1,$M$1:$M$16,MATCH($Q1,$N$1:$N$16,0)),""))

 

[barry houdini]

How about this method......

In Q1

=SMALL((L$1:L$16,N$1:N$16),ROWS(Q$1:Q1))

and then in R1

=INDIRECT(TEXT(SMALL(IF(L$1:N$16=Q1,ROW(L$1:N$16)*1000+COLUMN(L$1:N$16)-1),COUNTIF(Q$1:Q1,Q1)),"\R000\C000"),0)

confirmed with CTRL+SHIFT+ENTER

copy both down the column

 

[michael.collins22]

It works like a charm. Thanks for your help!

 

[Marcelo Branco]

How about this method......

In Q1

=SMALL((L$1:L$16,N$1:N$16),ROWS(Q$1:Q1))

and then in R1

=INDIRECT(TEXT(SMALL(IF(L$1:N$16=Q1,ROW(L$1:N$16)*1000+COLUMN(L$1:N$16)-1),COUNTIF(Q$1:Q1,Q1)),"\R000\C000"),0)

confirmed with CTRL+SHIFT+ENTER

copy both down the column

Barry,

This is very, very nice!!!

Still trying to understand the formula using Evaluate Formula (almost there ...)

M.