Filling an item name from rated and ranked cells

[GilsonJ]

I'm trying to get Excel to fill a column with item names that have been ranked according to a rating.

Table one: column A - there are five items in their original order (A,B,C,D,E), column B - their associated ratings (75,100,100,125,100).

Table two: column A - updated ranking of the items (1,2,2,2,5) using RANK(),column B - ordered ratings of those items (125,100,100,100,75) using LARGE(). In column C I would like to list the corresponding item (names i.e., D,B,C,E,A).

I have provided a screenshot of the layout for further clarity.

I tried using IFS() function to match ratings in table one and table two, then show the item in the same row from table one, but this doesn't work when items have the same ratings (i.e., B, C and E are all 100). [=IFS(I18=$D$18, $A$18, I18=$D$19, $A$19,I18=$D$20, $A$20,I18=$D$21,$A$21,I18=$D$22,$A$22)]

I also tried to print the address of the cell containing the matching rating in table one using CELL("address"), then use OFFSET()to print the corresponding item, but I also couldn't get that to work. [=OFFSET(CELL("address",INDEX($D18:$D$22,MATCH($I18,$D18:$D$22,0))),0,-3))]

I'm quite new to using formulae in excel so hopefully, this will be an easy fix for someone!

[#2 · Accepted solution]

I think this is what you're looking to do:

365 solution: =SORTBY(A18:A22,D18:D22,-1)

Non-365 solution (array enter): =INDEX($A$18:$A$22,SMALL(IF($D$18:$D$22=I18,ROW($A$18:$A$22)),COUNTIF($I$18:I18,I18))-17,1)

[#3]

Thank you so much @Patrick2788 both seem to have worked!!! Now I will go and read up on those functions to figure out how they work :)