Look up winners based on their scores: A Toastmasters contest in Excel

Do you need to pick out a name from a list based on some data? It might be the best-performing employee or department. Or the machine next in need of a service. Or the supplier whose concrete mix was the most consistent, or many others. This isn’t specifically an engineering application, but of general use:

The example we will look at here, comes from Toastmasters Division 74. The Toastmasters speaking contest’s judging form typically comes as a PDF file. It lets you fill it in but not do any calculations. A helpful person transferred the contest judging sheet to Excel. It sums the various scores for the different categories and automatically inserts the names of the winner and two runners-up. You will learn about the Lookup functions MATCH, INDEX, MAX, LARGE, IFERROR, and IF.

Let’s orientate ourselves by looking at a picture of the judging form:

Columns A-C have the judging items (categories).
Columns D-G contain suggested point values.
Columns H-Q are where you, the judge, fill in the contestants’ names and scores: Names on rows 4-5, scores per category on rows 7-10.

Totalling the Scores

Our first formula is on row 11, to calculate the Total Score for each contestant, but omit those who have no score. In cell H11: =IF(SUM(H7:H10)>0, SUM(H7:H10), “”)

This formula uses the functions IF and SUM. The SUM simply sums the scores in the cells above. The IF function takes three arguments: logical test, value if true, value if false.
Hence if the sum is greater than zero [SUM(H7:H10)>0] we show the sum, otherwise, a blank string [“”].

Another way to do it –perhaps more elegant– would be to use SUM only, and format the cell to hide zeros (or use a dash for zero). Press Ctrl+1 to pop up the Format Cells dialog. On the Number tab, choose Category = Custom. In the “Type” box, enter #0;”-“; and click OK.
The format is “format if >0; format if <0; format if =0”. With nothing after the second semicolon, nothing displays for a zero value. Format #0;; would also work. Ours displays a dash if the sum is negative, which it should never be.

Now let’s look at the formulas to display the winner and runners-up.

Computing the Winner

First, the winner: The full formula in B19: =IFERROR(INDEX($H$4:$Q$5,1,MATCH(MAX($H$11:$Q$11),$H$11:$Q$11,0)),””)

That formula for the winner contains several nested formulas: =IFERROR(INDEX($H$4:$Q$5,1,MATCH(MAX($H$11:$Q$11),$H$11:$Q$11,0)),””)

Let’s break it down: MAX($H$11:$Q$11) finds the maximum score of the sums in row 11.
Next, we have MATCH(MAX($H$11:$Q$11),$H$11:$Q$11,0)
MATCH returns the position number of an item in an array that matches the lookup value. The parameters are MATCH(Iookup_value, array, match_type). Match_type = 0 specifies an exact match. In other words, it takes the largest value and finds that it is (in this case) in the 5th column of the array (Astrid) and returns a 5.

Now for INDEX($H$4:$Q$5,1,MATCH(MAX($H$11:$Q$11),$H$11:$Q$11,0)):
The INDEX function returns a value (or the reference to a value) from within a table or range.
There are two forms of the INDEX function. We are using the simpler form, to return the value in a specified cell in a range of cells. The syntax is: INDEX(range, row_num, [column_num])

In this case, we look up in the blue range H4:Q5 (the contestant names in two merged rows), row 1. The column is set with the MATCH formula (5, in this particular case).

This could be improved to INDEX(H4:Q5, 1, MATCH( MAX(H11:Q11), H11:Q11, 0)):

• None of the dollars is necessary: This formula won’t be copied anywhere.
• If the INDEX range were just one row or column, it would need only one other parameter. Excel is clever enough not to need both row_num, and column_num. We can’t do that here, because rows 4:5 are merged for the contestants’ names.
• Notice also how much easier it would have been to understand the formula if, instead of cell addresses, the author had used range names. That is something we recommend.

(As an aside, the more complicated form of INDEX allows you to replace the “range” parameter with a series of ranges, separated by commas, and enclosed in parenthesis (round brackets). Then you need one extra parameter to say which of them to use. We won’t go there today.)

Finally, to keep it all tidy, we have the IFERROR function. If the first parameter returns an error value, IFERROR returns its second parameter, in this case “” (a blank string). Otherwise, it returns the (non-error) first parameter.

Computing the Runners-Up

These formulas seem, at first, more daunting:
B20 =IF(IFERROR(INDEX(H4:Q5,1,MATCH(LARGE(H11:Q11,2),H11:Q11,0)),””)=B19,””, IFERROR(INDEX(H4:Q5,1,MATCH(LARGE(H11:Q11,2),H11:Q11,0)),””)) and
B21 =IF(IFERROR(INDEX(H4:Q5,1,MATCH(LARGE(H11:Q11,3),H11:Q11,0)),””)=B20,””, IFERROR(INDEX(H4:Q5,1,MATCH(LARGE(H11:Q11,3),H11:Q11,0)),””))

Remember that H4:Q5 is the Contestants, while H11:Q11 contains their Scores. Let’s use Range Names to simplify matters. Then we get:
B20 =IF(IFERROR(INDEX(Contestants, 1, MATCH( LARGE(Scores,2),Scores,0)),””)=B19,””, IFERROR(INDEX(Contestants, 1, MATCH( LARGE(Scores,2),Scores,0)),””)) and
B21 =IF(IFERROR(INDEX(Contestants, 1, MATCH( LARGE(Scores,3),Scores,0)),””)=B20,””, IFERROR(INDEX(Contestants, 1, MATCH( LARGE(Scores,3),Scores,0)),””))

That is immediately more readable, even without the blue and green.

We notice that there are only two differences between the two lines:
1) =B20 in B21 vs. =B19 in B20 (in each case referring to the cell above), and
2) LARGE(Scores,3) in B21 vs. LARGE(Scores,2) in B20.

Notice that LARGE(Scores,2),Scores,0) replaces MAX in the Winner’s formula (B19). Where MAX gave us the highest value, LARGE(range, rank) gives us the value at the “rank” position in the ranked list. That is, LARGE(range, 1) gives us the biggest value (= MAX). Then LARGE(range, 2) gives us the second-largest value, and LARGE(range, 3) the third-largest value.

As with the winner, once LARGE has given us the score of the runner-up, MATCH gives its position in the range. Then INDEX looks up the name of that runner-up. Wrapping it in IFERROR makes sure we get a blank instead of an error.

For ease of understanding, we can simplify the formula in B20 to this:
B20 =IF(Second_Place=B19,””, Second_Place)
or
B20 =IF(Second_Place=Winner,””, Second_Place)
Similarly , for the third place:
B21 =IF(Third_Place=Second_Place,””, Third_Place)

Remember that the function is IF(logical_test, value_if_true, value_if_false)

So, in B21, if the third-placed name is the same as the second-placed, return a blank instead. That would be the case in a contest with only two contestants.

Similarly, in B20, if the second-placed is the same as the winner, return a blank instead. It would be unusual to have a contest with only one contestant!

In practice (but not in this spreadsheet), if there are three contestants, only the first and second places are awarded. If there are two contestants, only the winner is awarded.

Commentary: The author of the spreadsheet elected to do a tour de force. They (with much duplication) put the entire formula for each result in one cell each. But this is a good case for doing an intermediate calculation. Determine the Second_Place and Third_Place in another (hidden) cell. Then use a simple formula like B21: =IF(Third_Place=Second_Place,””, Third_Place) to compute the final answer. That is easier to understand and less prone to error.