Jan 03 2021 01:14 PM
Hi everyone,
I'm in a bit of a pickle and was hoping to get some help.
I am working on a project where I am trying to find the unique rank of something based on its score depending on a changing variable. The example I have here is finding the unique rank of a sports league based on its TV ratings depending on the month.
By using the drop down, I can change the month and I will get the correct rank. In order to find the rank, I created a "Rankings Table". The formula I used to populate my rankings table is:
=RANK.EQ(B3,B$3:B$6)+COUNTIF(B$3:B3,B3)-1
I created this rankings table so if I needed to add more months I could easily drag the rank across my newly added month(s).
To get the rank in my main table, the formula I used was:
=INDEX($J$3:$M$6,$I3,MATCH($D$1,$J$2:$M$2,0))
Next I wanted to create a dashboard element where I could use a drop down list to show the month which would in turn show me the rank of the league and the ratings for the league per the selected month.
To get my league name and its respective rating, the formulas I used were,
For league: =INDEX($A$3:$A$6,MATCH($A10,$F$3:$F$6,0),)
For ratings: =INDEX($B$3:$E$6,MATCH($B10,$A$3:$A$6,0),MATCH($A$9,$B$2:$E$2,0))
When I change the month from the drop down list, I realized that in order to get the correct order, I have to change the date from my main table as well which isn't very intuitive.
In order to get the correct order, I tried changing my formula so it would INDEX and MATCH from the "Rankings Table" instead of the "Rank" column in my main table but that is where I am getting stuck...
The Formula I used was: {=INDEX($A$3:$A$6,MATCH($A16,$J$3:$L$6,0),MATCH($A$15,$J$2:$L$2,0))}
I thought by using an array formula (ctrl+shift+enter), I would be able to MATCH the rank (1,2,3,4) across the entire table array for the rankings table but that doesn't work.
I have uploaded the file here and also posting a link to it here: Sports TV Ratings Rankings
Does anybody know how I can correctly get the values I require? Your help would truly be much appreciated. Thank you.
Regards,
Zaid Morad
Jan 03 2021 02:20 PM
In B10:
=INDEX($A$3:$A$6,MATCH($A10,INDEX($J$3:$M$6,,MATCH($A$9,$J$2:$M$2,0)),0))
In B16:
=INDEX($A$3:$A$6,MATCH($A10,INDEX($J$3:$M$6,,MATCH($A$15,$J$2:$M$2,0)),0))
See the attached version.
Jan 05 2021 04:24 PM