XLOOKUP With Duplicates

[excelbytes]

See the attached image. I'm using this formula to pull in the Set numbers, 1st largest, 2nd largest, etc., but the issue is when there are duplicate values. How do I get #4 to return Set 9 rather than Set 1 again?

Thanks,

 

[jasonb75]

Try using the SORT or SORTBY functions instead of XLOOKUP. I don't have a device with excel installed to hand in order to provide the correct syntax for the formula but one of those will do what you need, it may need some extra functions to limit the number of results returned if you only want top 6 rather than all records in the list.

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E
1	Game	ByCol1
2	Set1	12		Return Top:	6
3	Set2	1
4	set44	10		#1	Set7
5	Set4	2		#2	Set5
6	Set5	22		#3	Set9
7	set10	3		#4	Set1
8	Set7	50		#5	set44
9	Set8	3		#6	Set12
10	Set9	22
11	set22	5
12	Set11	6
13	Set12	7
Sheet3

Cell Formulas
Range	Formula
E4:E9	E4	=FILTER(SORT(FILTER(A2:B13,B2:B13>=LARGE(B2:B13,$E$2)),2,-1),{1,0})
Dynamic array formulas.

 

[jasonb75]

Following up on my earlier suggestion to try SORTBY, see if this works (untested).

=INDEX(SORTBY($A$2$:A$24,$B$2$:B$24,-1),SEQUENCE(6))

We can not see from the screen capture if B12:B23 are empty or if they contain formula blanks. In many cases this simple difference can affect the results of dependent formulas.

 

[jasonb75]

Borrowing the mini sheet kindly provided by @AhoyNC in order to show my suggested formula working (after correcting the typos). I've also made a slight change to allow more flexibility if more or less than 6 results are needed.

Book1
	A	B	C	D	E
1	Game	ByCol1
2	Set1	12		Return Top:	6
3	Set2	1
4	set44	10		1	Set7
5	Set4	2		2	Set5
6	Set5	22		3	Set9
7	set10	3		4	Set1
8	Set7	50		5	set44
9	Set8	3		6	Set12
10	Set9	22
11	set22	5
12	Set11	6
13	Set12	7
Sheet1

Cell Formulas
Range	Formula
D4:D9	D4	=SEQUENCE(E2)
E4:E9	E4	=INDEX(SORTBY($A$2:$A$24,$B$2:$B$24,-1),D4#)
Dynamic array formulas.

 

[excelbytes]

I like the formula =INDEX(SORTBY($A$2:A$24,$B$2:B$24,-1),SEQUENCE(6)) from jasonb75. I also came up with a more cumbersome one =SORTBY(FILTER(A2:A24,B2:B24>=LARGE(B2:B24,6)),FILTER(B2:B24,B2:B24>=LARGE(B2:B24,6)),-1)

Thanks for your help!

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E
1	Game	ByCol1
2	Set1	12		Return Top:	6
3	Set2	1
4	set44	10			Set7
5	Set4	2			Set5
6	Set5	22			Set9
7	set10	3			Set1
8	Set7	50			set44
9	Set8	3			Set12
10	Set9	22
11	set22	5
12	Set11	6
13	Set12	7
Data

Cell Formulas
Range	Formula
E4:E9	E4	=INDEX(SORT($A$2:$B$24,2,-1),SEQUENCE(E2))
Dynamic array formulas.