Win loss points calculation

Summary

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)

Generic formula

=VLOOKUP(result,points_table,2,0)

Explanation

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.

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.