Win loss points calculation

Generic formula

=VLOOKUP(result,points_table,2,0)

Related formulas

Map text to numbers

Nested IF function example

Explanation

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:

=VLOOKUP(C5,points_table,2,0)

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:

=IF(C5="Win",3,IF(C5="Loss",0,IF(C5="Tie",1)))

See this article for a detailed overview of nested IF formulas.