Finding a Unique Rank based on Drop Down Lists

%3CLINGO-SUB%20id%3D%22lingo-sub-2027995%22%20slang%3D%22en-US%22%3EFinding%20a%20Unique%20Rank%20based%20on%20Drop%20Down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027995%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20in%20a%20bit%20of%20a%20pickle%20and%20was%20hoping%20to%20get%20some%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20project%20where%20I%20am%20trying%20to%20find%20the%20unique%20rank%20of%20something%20based%20on%20its%20score%20depending%20on%20a%20changing%20variable.%20The%20example%20I%20have%20here%20is%20finding%20the%20unique%20rank%20of%20a%20sports%20league%20based%20on%20its%20TV%20ratings%20depending%20on%20the%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank1.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243744i3DD1F4900852179A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rank1.PNG%22%20alt%3D%22Rank1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBy%20using%20the%20drop%20down%2C%20I%20can%20change%20the%20month%20and%20I%20will%20get%20the%20correct%20rank.%20In%20order%20to%20find%20the%20rank%2C%20I%20created%20a%20%22Rankings%20Table%22.%20The%20formula%20I%20used%20to%20populate%20my%20rankings%20table%20is%3A%3C%2FP%3E%3CP%3E%3DRANK.EQ(B3%2CB%243%3AB%246)%2BCOUNTIF(B%243%3AB3%2CB3)-1%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank2.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243745i11C1B516AB11223F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Rank2.PNG%22%20alt%3D%22Rank2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20created%20this%20rankings%20table%20so%20if%20I%20needed%20to%20add%20more%20months%20I%20could%20easily%20drag%20the%20rank%20across%20my%20newly%20added%20month(s).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank3.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243746iDF57776258617A3F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Rank3.PNG%22%20alt%3D%22Rank3.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ETo%20get%20the%20rank%20in%20my%20main%20table%2C%20the%20formula%20I%20used%20was%3A%3C%2FP%3E%3CP%3E%3DINDEX(%24J%243%3A%24M%246%2C%24I3%2CMATCH(%24D%241%2C%24J%242%3A%24M%242%2C0))%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank4.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243747i0F7F98B8E0F34A41%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Rank4.PNG%22%20alt%3D%22Rank4.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENext%20I%20wanted%20to%20create%20a%20dashboard%20element%20where%20I%20could%20use%20a%20drop%20down%20list%20to%20show%20the%20month%20which%20would%20in%20turn%20show%20me%20the%20rank%20of%20the%20league%20and%20the%20ratings%20for%20the%20league%20per%20the%20selected%20month.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank5.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243748iDCBCA02E8CF1D548%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rank5.PNG%22%20alt%3D%22Rank5.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ETo%20get%20my%20league%20name%20and%20its%20respective%20rating%2C%20the%20formulas%20I%20used%20were%2C%3C%2FP%3E%3CP%3EFor%20league%3A%26nbsp%3B%3DINDEX(%24A%243%3A%24A%246%2CMATCH(%24A10%2C%24F%243%3A%24F%246%2C0)%2C)%3C%2FP%3E%3CP%3EFor%20ratings%3A%26nbsp%3B%3DINDEX(%24B%243%3A%24E%246%2CMATCH(%24B10%2C%24A%243%3A%24A%246%2C0)%2CMATCH(%24A%249%2C%24B%242%3A%24E%242%2C0))%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank6.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243749i29F99E3B95F16F62%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Rank6.PNG%22%20alt%3D%22Rank6.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhen%20I%20change%20the%20month%20from%20the%20drop%20down%20list%2C%20I%20realized%20that%20in%20order%20to%20get%20the%20correct%20order%2C%20I%20have%20to%20change%20the%20date%20from%20my%20main%20table%20as%20well%20which%20isn't%20very%20intuitive.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank7.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243750iE69192A7F6A5B4D2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Rank7.PNG%22%20alt%3D%22Rank7.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank8.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243751iD8FABA5C6EDBA2E4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Rank8.PNG%22%20alt%3D%22Rank8.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20order%20to%20get%20the%20correct%20order%2C%20I%20tried%20changing%20my%20formula%20so%20it%20would%20INDEX%20and%20MATCH%20from%20the%20%22Rankings%20Table%22%20instead%20of%20the%20%22Rank%22%20column%20in%20my%20main%20table%20but%20that%20is%20where%20I%20am%20getting%20stuck...%3C%2FP%3E%3CP%3EThe%20Formula%20I%20used%20was%3A%20%7B%3DINDEX(%24A%243%3A%24A%246%2CMATCH(%24A16%2C%24J%243%3A%24L%246%2C0)%2CMATCH(%24A%2415%2C%24J%242%3A%24L%242%2C0))%7D%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rank9.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243752i1B17AA9A8FD77F6F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Rank9.PNG%22%20alt%3D%22Rank9.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20thought%20by%20using%20an%20array%20formula%20(ctrl%2Bshift%2Benter)%2C%20I%20would%20be%20able%20to%20MATCH%20the%20rank%20(1%2C2%2C3%2C4)%20across%20the%20entire%20table%20array%20for%20the%20rankings%20table%20but%20that%20doesn't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20uploaded%20the%20file%20here%20and%20also%20posting%20a%20link%20to%20it%20here%3A%26nbsp%3B%3CA%20title%3D%22Sports%20TV%20Ratings%20Rankings%22%20href%3D%22https%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F1DM5Ugp8UuA-gVY7MgOAZzwKOO6-P3bur%2Fview%3Fusp%3Dsharing%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ESports%20TV%20Ratings%20Rankings%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anybody%20know%20how%20I%20can%20correctly%20get%20the%20values%20I%20require%3F%20Your%20help%20would%20truly%20be%20much%20appreciated.%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EZaid%20Morad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2027995%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2028080%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20a%20Unique%20Rank%20based%20on%20Drop%20Down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2028080%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F916491%22%20target%3D%22_blank%22%3E%40zaidmorad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B10%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24A%243%3A%24A%246%2CMATCH(%24A10%2CINDEX(%24J%243%3A%24M%246%2C%2CMATCH(%24A%249%2C%24J%242%3A%24M%242%2C0))%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20B16%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24A%243%3A%24A%246%2CMATCH(%24A10%2CINDEX(%24J%243%3A%24M%246%2C%2CMATCH(%24A%2415%2C%24J%242%3A%24M%242%2C0))%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2034808%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20a%20Unique%20Rank%20based%20on%20Drop%20Down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2034808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU%20SO%20MUCH!%20You're%20the%20best!%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

Rank1.PNG

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

Rank2.PNG

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).

Rank3.PNG

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))

Rank4.PNG

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.

Rank5.PNG

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))

Rank6.PNG

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.

Rank7.PNGRank8.PNG

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))}

Rank9.PNG

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

2 Replies

@zaidmorad 

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.

@Hans Vogelaar 

 

THANK YOU SO MUCH! You're the best!