Break Tied Ranks & Display Sorted Leaderboards (Helper Key & SORTBY)

Why a tied score can repeat the same name

MATCH with exact matching returns the first matching position. If the second- and third-highest revenues both equal 85000, using LARGE followed by MATCH on revenue alone can return Ava twice. A tie needs an explicit ordering policy.

Our policy is revenue descending, then margin descending, then stable numeric ID ascending. Keep the ID with its record when moving rows. It is a final ordering key, not a measure of performance. This gives unique positions even when both performance metrics tie.

C2:C5 stores percentage points: enter 42 for a 42% margin, not the fraction 0.42. The example allows 0..100 points. Revenue can be any numeric value, including negative adjustments; IDs must be numeric and unique. Names are display labels and need not be unique.

Avoid adding a tiny number to the primary score

A helper such as revenue + margin/10000 can reverse the primary ordering. For example, 100 + 100/10000 is 100.01 and beats 100.001 + 0/10000, even though 100.001 is the higher revenue. An arbitrary divisor is not a safe general tie-breaker.

Decimal offsets can also disappear at large magnitudes because of numeric precision, and equal secondary values still collide. Keep the keys separate. The helper below counts preceding records instead of perturbing the revenue.

Validate the example inputs once

H2 contains this shared audit. Fix its message before reading the leaderboard. The four-row ranges are deliberate; extend every source, audit and helper range together when adding records. Blank or text numeric inputs and duplicate IDs are rejected.

=IF(COUNT(B2:D5)<>12,"Enter numeric revenue, margin and ID",IF(OR(MIN(C2:C5)<0,MAX(C2:C5)>100),"Margin must be 0..100 points",IF(OR(COUNTIF(D2:D5,D2)>1,COUNTIF(D2:D5,D3)>1,COUNTIF(D2:D5,D4)>1,COUNTIF(D2:D5,D5)>1),"IDs must be unique","OK")))

Sort by three keys

H4 contains this compact display. Initially the result is Noah > Ava > Leo > Mia. Raising Leo’s margin to 50 puts Leo ahead of Ava. If their margins are equal, ID 101 for Ava precedes ID 103 for Leo.

For a separate output table, use =SORTBY(A2:D5,B2:B5,-1,C2:C5,-1,D2:D5,1) after the audit says OK. Place it outside the source data with four empty columns and four empty rows. This keeps each name, revenue, margin and ID together.

=IF($H$2<>"OK",$H$2,TEXTJOIN(" > ",TRUE,SORTBY(A2:A5,B2:B5,-1,C2:C5,-1,D2:D5,1)))

Unique helper positions without SORTBY

E2 contains the formula below; fill down to E5. It counts records with greater revenue, plus equal revenue and greater margin, plus equal revenue and margin and a smaller ID. The fixed source ranges stay in place while the current-row references change.

The initial helper positions in E2:E5 are 2, 1, 3, 4. These are unique display positions, not shared competition ranks. For the output list, put 1, 2, 3, 4 in J2:J5 and enter =IF($H$2<>"OK",$H$2,INDEX($A$2:$A$5,MATCH(J2,$E$2:$E$5,0))) in K2, then fill down.

This helper works without SORTBY but scans the data for each record. Prefer multi-key sorting for large lists; avoid copying this fixed-range demonstration over entire worksheet columns.

=IF($H$2<>"OK",$H$2,1+COUNTIFS($B$2:$B$5,">"&B2)+COUNTIFS($B$2:$B$5,B2,$C$2:$C$5,">"&C2)+COUNTIFS($B$2:$B$5,B2,$C$2:$C$5,C2,$D$2:$D$5,"<"&D2))

Further reading

• Microsoft Support — SORTBY function