RANK with INDEX MATCH

[tigerzen]

Trying to create a dynamic lookup which will rank the results. Idea is to select a year and then have the numbers ranked. Column J is my initial attempt so please ignore, but I'm helping an Excel 2019 user with Index Match so if the solution could incorporate INDEX MATCH great, if I'm off the mark and there is a quicker function then I'll take that.

Book3
	A	B	C	D	E	F	G	H	I	J
1	Name	2020	2021	2022		Year required -->	2021	Rank
2	Tom	47	31	49				3		31
3	Jack	20	49	16				1		49
4	Fred	12	17	28				4		17
5	Peter	11	44	44				2		44
Sheet3

Cell Formulas
Range	Formula
J2:J5	J2	=INDEX($B$2:$D$5,,MATCH($G$1,$B$1:$D$1,0))
H2:H5	H2	=RANK(C2,$C$2:$C$5)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G1	List	=$B$1:$D$1

 

[tigerzen]

Came up with this in column J, looks a little long-winded though.

Book3
	A	B	C	D	E	F	G	H	I	J
1	Name	2020	2021	2022		Year required -->	2021	Rank
2	Tom	47	31	49				3		3
3	Jack	20	49	16				1		1
4	Fred	12	17	28				4		4
5	Peter	11	44	44				2		2
Sheet3

Cell Formulas
Range	Formula
H2:H5	H2	=RANK(C2,$C$2:$C$5)
J2:J5	J2	=RANK(INDEX($B$2:$D$5,ROWS($A$2:A2),MATCH($G$1,$B$1:$D$1,0)),INDEX($B$2:$D$5,,MATCH($G$1,$B$1:$D$1,0)))

Cells with Data Validation
Cell	Allow	Criteria
G1	List	=$B$1:$D$1

 

[Peter_SSs]

Like this?

23 08 28.xlsm
	A	B	C	D	E	F	G	H
1	Name	2020	2021	2022		Year required -->	2021	Rank
2	Tom	47	31	49				3
3	Jack	20	49	16				1
4	Fred	12	17	28				4
5	Peter	11	44	44				2
Rank

Cell Formulas
Range	Formula
H2:H5	H2	=RANK(INDEX(B2:D2,MATCH(G$1,B$1:D$1,0)),INDEX(B$2:D$5,0,MATCH(G$1,B$1:D$1,0)))

 

[tigerzen]

Like this?

23 08 28.xlsm
	A	B	C	D	E	F	G	H
1	Name	2020	2021	2022		Year required -->	2021	Rank
2	Tom	47	31	49				3
3	Jack	20	49	16				1
4	Fred	12	17	28				4
5	Peter	11	44	44				2
Rank

Cell Formulas
Range	Formula
H2:H5	H2	=RANK(INDEX(B2:D2,MATCH(G$1,B$1:D$1,0)),INDEX(B$2:D$5,0,MATCH(G$1,B$1:D$1,0)))

Perfect, knew there had to be a shorter way.

 

[Peter_SSs]

Cheers!