Mar 10 2022 07:02 AM
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!
Mar 10 2022 08:33 AM
SolutionMar 10 2022 08:36 AM
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 :)
Mar 10 2022 08:33 AM
Solution