Sorting several "0" values / Xlookup

[dbx]

Hello all,

I'm trying to create a small dashboard in Excel where I could monitor monthly sales results of 3 clients - Player 1, Player 2, Player 3
I need to see their names sorted based on their numbers and It's easy to do it when they have values bigger than zero.

Here is what I did:

A​	B​	C​	D
Player 1	15​	=LARGE($B1:$B3;1) -> 26	=XLOOKUP(C1;$B1:$B3;$A1:$A3) -> Player 3
Player 2	3​	=LARGE($B1:$B3;2) -> 15	=XLOOKUP(C2;$B1:$B3;$A1:$A3) -> Player 1
Player 3	26​	=LARGE($B1:$B3;3) -> 3	=XLOOKUP(C3$B1:$B3;$A1:$A3) -> Player 2

The problem comes when two of the clients have 0 (zero) values:

Player 1 -> 15
Player 2 -> 0
Player 3 -> 0

Then the XLOOKUP function doesn't work properly and I see the following:

Player 1 -> 15
Player 2 -> 0
Player 2 -> 0

I need to see the names of all clients, no matter what their values are:

Player 1 -> 15
Player 2 -> 0
Player 3 -> 0

Please, advise if there is a solution to do it with a formulas.

Thanks in advance.

BR,
Nick

 

[shaowu459]

how about below formula, i see it is a sort problem:

工作簿2.xlsx
	A	B	C	D	E
1	A	B
2	Player 1	15	Player 3	26
3	Player 2	3	Player 1	15
4	Player 3	26	Player 2	3
5
6	A	B
7	Player 1	15	Player 1	15
8	Player 2	0	Player 2	0
9	Player 3	0	Player 3	0
10
Sheet1

Cell Formulas
Range	Formula
C2:D4,C7:D9	C2	=SORT(A2:B4,2,-1)
Dynamic array formulas.

 

[dbx]

Works fine! Many thanks

 

[shaowu459]

Works fine! Many thanks

thanks for your feedback