Search code examples
excelif-statementgoogle-sheetsexcel-formulagoogle-sheets-formula

Combining IF statements and match statements in google sheets

Cheltenham spreadsheet

Context

The purpose of the below spreadsheet is to calculate the amount of points gamblers are making over cheltenham.

Each participant picks one horse a day for every race.

  • If there horse finishes 1st they get 5 points
  • 2nd 3 points
  • 3rd 1 point

Spreadsheet

Name Race 1 Race 2 Race 3 Race 4 Race 5 Race 6 Race 7 Points Race 1 results Race 2 results Race 3 results Race 4 results Race 5 results Race 6 results Race 7 results Race 8 results
Daniel 1 5 17 24 3 2 8 1800 1st place 2 1 3 1 2 12 2 7
Bob 2 23 5 2 24 4 1 2nd place 3 5 5 2 24 3 3 6
Mark 6 5 7 5 1 7 3 3rd place 1 4 12 3 1 1 4 5
Luke 8 4 9 3 6 0 5
etc

Problem

I am trying to match the players guesses to the results inputted on the right so that I will get a total column calculating the points.

Attempt

This is what I was trying but am not getting correct results as highlighted by first Points cell.

=IF(B2=K2,I2+5,If(B2=K3,I2+3,If(B2=K4,I2+1)))

Microsoft excel

Is there an easier way of doing this in excel?

Solution

  • For each player and each course, try

    =choose(iferror(MATCH(B2,K$2:K$4,0),0)+1,0,5,3,1)

    then sum each row

    enter image description here