SOLVED

Sort Scores, Return Corresponding Names

%3CLINGO-SUB%20id%3D%22lingo-sub-1717765%22%20slang%3D%22en-US%22%3ESort%20Scores%2C%20Return%20Corresponding%20Names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1717765%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20everyone%2C%20would%20appreciate%20any%20help%20you%20can%20provide.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrying%20to%20sort%20scores%20from%20highest%20to%20lowest%20and%20then%20return%20the%20corresponding%20name%20values%20in%20sorted%20order.%20For%20example%2C%20Team%201%20has%2010%20points%2C%20Team%202%20has%2011%20points%2C%20Team%203%20has%205%20points.%20I%20want%20it%20to%20sort%20Team%202%2C%20Team%201%2C%20Team%203.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20more%20trouble%20than%20I%20expected.%20Thanks%20in%20advance!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1717765%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1717786%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20Scores%2C%20Return%20Corresponding%20Names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1717786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F810862%22%20target%3D%22_blank%22%3E%40mjlipari188%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20solution%20using%20two%20helper%20columns%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S3543.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222443i355AD319ACC1E4F9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22S3543.png%22%20alt%3D%22S3543.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EC4%20contains%20the%20formula%3C%2FP%3E%0A%3CP%3E%3DRANK(B4%2C%24B%244%3A%24B%249)%2BCOUNTIF(B%244%3AB4%2CB4)-1%3C%2FP%3E%0A%3CP%3EThis%20is%20filled%20down%20to%20C9.%20The%20formula%20returns%20the%20rank%2C%20breaking%20ties%20(note%20the%20different%20rank%20for%20Team%204%20and%20Team%205).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EE4%20to%20E9%20contain%20the%20numbers%201%20to%206.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formulas%20in%20F4%20and%20G4%20are%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24A%244%3A%24A%249%2CMATCH(E4%2C%24C%244%3A%24C%249%2C0))%3C%2FP%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24B%244%3A%24B%249%2CMATCH(E4%2C%24C%244%3A%24C%249%2C0))%3C%2FP%3E%0A%3CP%3Erespectively.%20These%20are%20filled%20down%20to%20F9%20and%20G9.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1717787%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20Scores%2C%20Return%20Corresponding%20Names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1717787%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%3BMakes%20so%20much%20sense%2C%20thank%20you%20for%20you%20help!!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hey everyone, would appreciate any help you can provide.

 

Trying to sort scores from highest to lowest and then return the corresponding name values in sorted order. For example, Team 1 has 10 points, Team 2 has 11 points, Team 3 has 5 points. I want it to sort Team 2, Team 1, Team 3. 

 

Having more trouble than I expected. Thanks in advance! 

2 Replies
best response confirmed by mjlipari188 (New Contributor)
Solution

@mjlipari188 

 

Here is a solution using two helper columns:

 

S3543.png

 

C4 contains the formula

=RANK(B4,$B$4:$B$9)+COUNTIF(B$4:B4,B4)-1

This is filled down to C9. The formula returns the rank, breaking ties (note the different rank for Team 4 and Team 5).

 

E4 to E9 contain the numbers 1 to 6.

 

The formulas in F4 and G4 are

=INDEX($A$4:$A$9,MATCH(E4,$C$4:$C$9,0))

and

=INDEX($B$4:$B$9,MATCH(E4,$C$4:$C$9,0))

respectively. These are filled down to F9 and G9.

 

See the attached sample workbook.

@Hans Vogelaar Makes so much sense, thank you for you help!!!