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.
This challenge breaks the job into two parts. We need to mark the agent in second place inside the table, and we also need to return the numeric value for second place in the summary.
How We Solve It
The cleanest route is to find the second-largest revenue with LARGE, then compare each row against its rank. 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: RANK to label the row
Method 2: Rank each value so the second-place row can be labeled.
To flag the correct agent, we rank each revenue against the whole range. When the rank equals 2, the row gets RUNNER UP. Every other row becomes STANDARD.
This solves the row-label part of the problem because the formula checks each person against the full leaderboard, not against just one neighbor. Once the rank is known, the sheet can mark the exact row that belongs to the second-place seller.
=IF(RANK(B2, $B$2:$B$6) = 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.
Learn more this functionLARGE
2. RANK
RANK tells each value where it stands against the rest of the list. That is what lets us mark one row as the runner-up inside the table.
Learn more this functionRANK
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.
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.