Author: Oscar Cronquist Article last updated on October 27, 2018

Here comes another post about the MMULT function, today I made a dynamic scoreboard.

There are five women competing and there are 8 rounds. Look what happens with the scoreboard as I type new values in round 3.

Dynamic scoreboard1

Here is the array formula in cell C1:

=INDEX($C$11:$K$15, MATCH(LARGE(MMULT(IF($D$11:$K$15<>"", $D$11:$K$15,0), {1;1;1;1;1;1;1;1}), ROWS($A$1:A1)), (COUNTIF($C$2:C2, $C$11:$C$15)=0)*MMULT(IF($D$11:$K$15<>"", $D$11:$K$15, 0), {1;1;1;1;1;1;1;1}), 0), COLUMNS($A$1:A1))

The formula works even if two or more people share the same total score.

Want to know more about the functions in the array formula above? INDEX, MATCH, COUNTIF, ROW, MMULT and LARGE

How to enter an array formula

  1. Copy array formula above (CTRL + c)
  2. Select cell C1
  3. Click in formula bar
  4. Paste array formula to formula bar
  5. Press and hold CTRL + SHIFT simultaneoulsy
  6. Press Enter once
  7. Release all keys

If you did it right the formula in the formula bar now begins and ends with a curly brackets, like this ={array_formula}. Don´t type these characters yourself.

Hiding zeros

I have hidden zeros in cell range D3:K7 by applying cell formatting, these are the steps:

  1. Select cell range D3:K7
  2. Press CTRL + 1
  3. Go to tab "Number"
  4. Click "Custom"
  5. Type: 0;-0;;@
  6. Click OK

Interested in learning more about array formulas? Join my Advanced excel course.

Download excel *.xlsx file

Dynamic scoreboard.xlsx