Combine XLOOKUP and RANK to create ranked dynamic table from static alphabetical table?

[chasfh]

I have a static table of players in alphabetical order with scores and ranks. I want to create a dynamic second table that reflects the same table in ranked order of points.

My first instinct is that it would combine XLOOKUP and RANK into a single command, but that may not be the way to go, because I am coming up dry when I google around for such a command.

Here is the table in alphabetical order:

Player	Score	Rank
Barrett, S	84	11
Clark, R	86	8
Duski, M	86	8
Eikenberry, J	84	11
Helseth, T	89	3
Hopper, B	89	3
Hultquist, B	89	3
James, B	89	3
Kay, G	89	3
Michel, G	85	10
Muntz, R	94	1
Rankin, T	83	14
Rosen, R	84	11
Stephens, C	94	1
Tellman, M	82	15

The result would look something like this:

Player	Score	Rank
Muntz, R	94	1
Stephens, C	94	1
Helseth, T	89	3
Hopper, B	89	3
Hultquist, B	89	3
James, B	89	3
Kay, G	89	3
Clark, R	86	8
Duski, M	86	8
Michel, G	85	10
Barrett, S	84	11
Eikenberry, J	84	11
Rosen, R	84	11
Rankin, T	83	14
Tellman, M	82	15

The main conditions for this new table must be these:

• It has to be dynamic so that when scores and ranks change, this second table reflects the new rankings, changing the order of players listed as appropriately ranked.
• When there is a tie score between or among multiple records, all the tied players must show in the table. For example, there is a five-way tie for third on the example table—in the dynamic table result, all five players must be reflected, as opposed to the same player showing up five times.

Is this possible to do?

 

[LogChief]

Try this:

=SORT(A2:C16, 2, -1)

or this if you want ties in alphabetical order:

=SORT(A2:C16,{2,1},{-1,1})

 

[alansidman]

Power Query Solution

Book8
	A	B	C	D	E	F	G
1	Player	Score	Rank		Player	Score	Rank
2	Barrett, S	84	11		Muntz, R	94	1
3	Clark, R	86	8		Stephens, C	94	1
4	Duski, M	86	8		Helseth, T	89	3
5	Eikenberry, J	84	11		Hopper, B	89	3
6	Helseth, T	89	3		Hultquist, B	89	3
7	Hopper, B	89	3		James, B	89	3
8	Hultquist, B	89	3		Kay, G	89	3
9	James, B	89	3		Clark, R	86	8
10	Kay, G	89	3		Duski, M	86	8
11	Michel, G	85	10		Michel, G	85	10
12	Muntz, R	94	1		Barrett, S	84	11
13	Rankin, T	83	14		Eikenberry, J	84	11
14	Rosen, R	84	11		Rosen, R	84	11
15	Stephens, C	94	1		Rankin, T	83	14
16	Tellman, M	82	15		Tellman, M	82	15
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Score", Order.Descending}})
in
    #"Sorted Rows"

any changes to the source document will dynamically update the output when you select Data-->Refresh Data

 

[LogChief]

Or if you need to include rank in the formula:

=LET(
    a, A2:A16,
    b, B2:B16,
    c, RANK.EQ(--b, b),
    SORT(HSTACK(a, b, c), {2, 1}, {-1, 1})
)