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

mike4president

New Member
Joined
May 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
1619890100417.png


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



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!
 

Attachments

  • 1619890641149.png
    1619890641149.png
    14.9 KB · Views: 1
  • 1619890725567.png
    1619890725567.png
    16.8 KB · Views: 4
  • 1619890760927.png
    1619890760927.png
    19 KB · Views: 5

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,613
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCDEF
1
21Text1
32Text2
43Text3
54Text4
65Text5
76Text6
87Text7
98Text8
109Text9
1110Text10
1211Text11
1312Text12
1413Text13
1514Text14
1615Text15
1716Text16
1817Text17
1918Text18
2019Text19
21
22
23RankOutput
241Text3Text5Text3Text4
252Text10text10text11text9
261Text2text2text4
Lists
Cell Formulas
RangeFormula
B24:B26B24=FILTER($B$2:$B$20,$A$2:$A$20=SMALL(XLOOKUP(C24:F24,$B$2:$B$20,$A$2:$A$20,""),A24))
 

mike4president

New Member
Joined
May 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCDEF
1
21Text1
32Text2
43Text3
54Text4
65Text5
76Text6
87Text7
98Text8
109Text9
1110Text10
1211Text11
1312Text12
1413Text13
1514Text14
1615Text15
1716Text16
1817Text17
1918Text18
2019Text19
21
22
23RankOutput
241Text3Text5Text3Text4
252Text10text10text11text9
261Text2text2text4
Lists
Cell Formulas
RangeFormula
B24:B26B24=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,613
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,623
Messages
5,719,801
Members
422,245
Latest member
Kebad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top