This tutorial shows how to work Win loss points calculation in Excel using the example below;
To assign points based on win/loss/tie results for a team, you can use a simple VLOOKUP formula, or a nested IF formula, as explained below.
In the example shown, the formula in D5 is:
How this formula works
This is a very simple application of the VLOOKUP function set for “exact match”:
- lookup value comes from C5
- table array is the named range “points_table” (F5:G7)
- column index is 2, since points are in column G
- range lookup is 0 (FALSE) to force exact match
Because we are using VLOOKUP in exact match mode, the points table does not need to be sorted in any particular way.
VLOOKUP is a nice solution in this case if you want to display the points table as a “key” for reference on the worksheet. If you don’t need to do this, see the nested IF version below.
Nested IF version
To calculate win/loss/tie points with the IF function, you can use a simple nested IF: