Returning a text value based on it's relative rank in a range

[mike4president]

Hello,
I'm trying to create a relative ranking structure based on cell values within a repeating range.

I have ranked the text values accordingly.

I then have a sheet with columns that contain subsets of these text values like so;

What I am trying to accomplish, is that when a Rank of 1 is entered into a cell in row A, it will return the highest value of that row based on the column values in row B. So as an example;

If 1 was entered into cell A2, it would return "Text 1" in B2. If 2 was entered in A2 it would return "Text 3" in B2. If 3 was entered in A2 it would return "text 5". If any other rank (4-20) was entered B2 would remain blank. The same would happen in Row 3 except it would consider "Text 2" as rank 1, "Text 3" as rank 2, and "Text 4" as rank 3.

I can't seem to get this to work properly with any combination of COUNTIF or Rank, any help here would be extremely appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	1	Text1
3	2	Text2
4	3	Text3
5	4	Text4
6	5	Text5
7	6	Text6
8	7	Text7
9	8	Text8
10	9	Text9
11	10	Text10
12	11	Text11
13	12	Text12
14	13	Text13
15	14	Text14
16	15	Text15
17	16	Text16
18	17	Text17
19	18	Text18
20	19	Text19
21
22
23	Rank	Output
24	1	Text3	Text5		Text3	Text4
25	2	Text10		text10	text11	text9
26	1	Text2		text2		text4
Lists

Cell Formulas
Range	Formula
B24:B26	B24	=FILTER($B$2:$B$20,$A$2:$A$20=SMALL(XLOOKUP(C24:F24,$B$2:$B$20,$A$2:$A$20,""),A24))

 

[mike4president]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	1	Text1
3	2	Text2
4	3	Text3
5	4	Text4
6	5	Text5
7	6	Text6
8	7	Text7
9	8	Text8
10	9	Text9
11	10	Text10
12	11	Text11
13	12	Text12
14	13	Text13
15	14	Text14
16	15	Text15
17	16	Text16
18	17	Text17
19	18	Text18
20	19	Text19
21
22
23	Rank	Output
24	1	Text3	Text5		Text3	Text4
25	2	Text10		text10	text11	text9
26	1	Text2		text2		text4
Lists

Cell Formulas
Range	Formula
B24:B26	B24	=FILTER($B$2:$B$20,$A$2:$A$20=SMALL(XLOOKUP(C24:F24,$B$2:$B$20,$A$2:$A$20,""),A24))

That did the trick thank you!

 

[Fluff]

You're welcome & thanks for the feedback.