Simple Sorting Question

[RomoBot]

I know there's a simple solution to this, but I am too green to figure it out.

Basically, I have this range:

9	6	3	1	4	2	8	5	7
A	B	C	D	E	F	G	H	I
24	13	15	18	10	13	15	20	16

And I want to in a new cell a function to find the highest two based on Row 3 to look like this

9	5
A	H
24	20

 

[JGordon11]

Book1
	A	B	C	D	E	F	G	H	I	J
1	9	6	3	1	4	2	8	5	7
2	A	B	C	D	E	F	G	H	I
3	24	13	15	18	10	13	15	20	16
4
5
6	9	5
7	A	H
8	24	20
9
Sheet3

Cell Formulas
Range	Formula
A6:B8	A6	=INDEX($A$1:$I$3,SEQUENCE(3),MATCH(LARGE($A$3:$I$3,SEQUENCE(1,2)),$A$3:$I$3,0))
Dynamic array formulas.

 

[Eric W]

Or:

=INDEX(SORT(A1:I3,3,-1,1),{1;2;3},{1,2})

 

[RomoBot]

=INDEX($A$1:$I$3,SEQUENCE(3),MATCH(LARGE($A$3:$I$3,SEQUENCE(1,2)),$A$3:$I$3,0))

I'm getting a #NAME error, I don't seem to have the SEQUENCE function, even though I have excel 2021?
Incidentally, same goes for SORT function, so @Eric W 's solution won't work either...

 

[JGordon11]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J
1	9	6	3	1	4	2	8	5	7
2	A	B	C	D	E	F	G	H	I
3	24	13	15	18	10	13	15	20	16
4
5
6	9	5
7	A	H
8	24	20
9
Sheet3

Cell Formulas
Range	Formula
A6:B8	A6	=INDEX($A$1:$I$3,ROW(1:3),MATCH(LARGE($A$3:$I$3,COLUMN(A:B)),$A$3:$I$3,0))
Dynamic array formulas.

 

[Eric W]

Well, that's surprising! I would have thought that Excel 2021 would have the dynamic functions in it. Without them, you'd need to get a little more complicated:

Book2
	A	B	C	D	E	F	G	H	I
1	9	6	3	1	4	2	8	5	7
2	A	B	C	D	E	F	G	H	I
3	24	13	15	18	10	13	15	20	16
4
5
6	9	5
7	A	H
8	24	20
Sheet11

Cell Formulas
Range	Formula
A6:B7	A6	=INDEX($A1:$I1,AGGREGATE(15,6,(COLUMN($A1:$I1)-COLUMN($A1)+1)/($A$3:$I$3=A$8),COUNTIF($A$8:A$8,A$8)))
A8:B8	A8	=LARGE($A$3:$I$3,COLUMNS($A8:A8))

JGordon11, if the OP doesn't have SEQUENCE, I'd be surprised if the SPILL functionality works. Also, your formula has trouble if there are duplicate values. Put 24 in D3 to see what I mean.

 

[RomoBot]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J
1	9	6	3	1	4	2	8	5	7
2	A	B	C	D	E	F	G	H	I
3	24	13	15	18	10	13	15	20	16
4
5
6	9	5
7	A	H
8	24	20
9
Sheet3

Cell Formulas
Range	Formula
A6:B8	A6	=INDEX($A$1:$I$3,ROW(1:3),MATCH(LARGE($A$3:$I$3,COLUMN(A:B)),$A$3:$I$3,0))
Dynamic array formulas.

This will work!!!

Thanks!

EDITED: I think I got the spill response. Trying over in google sheets, maybe that will work?

 

[JGordon11]

Well, that's surprising! I would have thought that Excel 2021 would have the dynamic functions in it. Without them, you'd need to get a little more complicated:

Book2
	A	B	C	D	E	F	G	H	I
1	9	6	3	1	4	2	8	5	7
2	A	B	C	D	E	F	G	H	I
3	24	13	15	18	10	13	15	20	16
4
5
6	9	5
7	A	H
8	24	20
Sheet11

Cell Formulas
Range	Formula
A6:B7	A6	=INDEX($A1:$I1,AGGREGATE(15,6,(COLUMN($A1:$I1)-COLUMN($A1)+1)/($A$3:$I$3=A$8),COUNTIF($A$8:A$8,A$8)))
A8:B8	A8	=LARGE($A$3:$I$3,COLUMNS($A8:A8))

JGordon11, if the OP doesn't have SEQUENCE, I'd be surprised if the SPILL functionality works. Also, your formula has trouble if there are duplicate values. Put 24 in D3 to see what I mean.

Eric, as usual, you are correct. RomoBot - note that my formula won't work if there is a tie for the largest value in row 3. Which brings up another problem, what would you want to display if there are three or more tied for number 1?

 

[RomoBot]

Eric, as usual, you are correct. RomoBot - note that my formula won't work if there is a tie for the largest value in row 3. Which brings up another problem, what would you want to display if there are three or more tied for number 1?

I'd need it to display the three tied numbers. If it makes it easier, I could have the whole spread re-display in numerical order?