The Problem
Finding the top seller is easy enough, but many scoreboards also need the runner-up. That second-place result matters when there is an award, a bonus, or a shortlist that goes beyond just the number one position.
The tricky part is that second place is not just the largest value after sorting by eye. The workbook needs a repeatable rule that can identify the runner-up row, return the exact second-best number, and combine the top two values for the awards summary.
The flow below shows that leaderboard logic. The ranking step creates both the row label and the summary numbers, so the table and summary stay connected.
The Problem: Runner-Up Results Need Ranking Logic Second place should be calculated from the leaderboard, not picked by visual scanning.
In this workbook, each sales agent has one revenue total. The challenge is to label the runner-up inside the table, calculate the combined revenue of the top two agents, and return the exact second-best revenue value.
- The row label should identify the second-place agent.
- The summary should combine first and second place revenue.
- The summary should also show the runner-up revenue by itself.
That makes the leaderboard easier to review. The award label shows who earned the runner-up position, while the summary gives the numbers needed for reporting.
How We Solve It
The cleanest route is to find the second-largest revenue with LARGE, then compare each row against the leaderboard position. Once that is done, the top-two total is just a simple combination of the two largest values.
Method 1: LARGE for the runner-up value
Method 1: Return the second-largest revenue value with LARGE.
LARGE is a natural fit here because it lets us ask for the 1st, 2nd, 3rd, or any other position in a sorted list without actually sorting the sheet. Asking for position 2 gives the second-best revenue directly.
This solves the summary part of the challenge because the worksheet needs the exact second-place revenue in its own cell. Instead of rearranging the table, the formula pulls that runner-up number straight from the revenue range.
=LARGE($B$2:$B$6, 2)
Method 2: Count Higher Values to Label the Row
Method 2: Count how many values are higher so the second-place row can be labeled.
To flag the correct agent, count how many revenue values are higher than the current row. If exactly one value is higher, the current row is in second place and should get the RUNNER UP label.
This solves the row-label part of the problem because the formula checks each person against the full leaderboard, not against just one neighbor. It also works well in this workbook because it avoids depending on a separate ranking function.
=IF(COUNTIF($B$2:$B$6,">"&B2)+1=2,"RUNNER UP","STANDARD")
Method 3: Pull the name with sorting tools
Method 3: Sort the list and pull the second row when you want the runner-up name directly.
If you also want the runner-up name in one formula, modern Excel makes that possible with SORT plus INDEX. It is not required for the validator here, but it is a useful extension of the same ranking idea.
This solves a related reporting need where the runner-up person matters just as much as the value. After sorting the table by revenue, the second row becomes the person in second place, so the formula can return the name directly.
=INDEX(SORT(A2:B6, 2, -1), 2, 1)
Function Explanation
1. LARGE
LARGE returns the nth largest value from a range. In this challenge it gives us the exact revenue number for second place.
That is more reliable than sorting the table manually because the formula keeps working when revenue values change or rows are updated.
Learn more this functionLARGE
2. COUNTIF
COUNTIF can count how many values are greater than the current row. Adding 1 to that count gives the row's leaderboard position.
The row label depends on each value being compared with the full revenue range, not just the neighboring rows. That keeps the award logic tied to the whole leaderboard.
3. SUM
The summary for the top two revenues can be built by summing the first and second largest values together. This keeps the logic clear and avoids extra helper columns.
Using two LARGE calls is easy to audit because the formula clearly points to first place and second place before adding them.
One thing to think about is ties. If two people share the same value, rank-based formulas can assign the same position twice, so tie rules matter in real leaderboards.
Mark the second-place seller in column C, then finish the summary so the sheet shows both the combined revenue of the top two agents and the exact value of second place.