Exceljet

Quick, clean, and to the point

Win loss points calculation

Excel formula: Win loss points calculation
Generic formula 
=VLOOKUP(result,points_table,2,0)
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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.