Excel formula: Win loss points calculation

Generic formula

=VLOOKUP(result,points_table,2,0)

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)

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

Related formulas

Map text to numbers

This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate "2nd column", and zero as the last argument to force an exact match. Although in this case we are mapping text values to...

Nested IF function example

This article describes the Excel nested IF construction. Usually, nested IFs are used when you need to test more than one condition and return different results depending on those tests. Testing more than one condition If you need to test for more...

Longest winning streak

This is a tricky formula to understand, and it requires that you have a numeric id for each match, and that all matches are sorted by id. They key is in understanding how FREQUENCY gathers numbers into "bins". Each bin represents an upper limit, and...

Calculate win loss tie totals

The goal in this example is to calculate total wins, losses, and ties for each team listed in column G. The problem is complicated somewhat by the fact that a team can appear in either column B or C, so we need to take this into account when...

Related functions

Excel VLOOKUP Function

VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...

Excel IF Function

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...

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.

500 Formulas | 101 Functions

Exceljet

Quick, clean, and to the point

Win loss points calculation

Nested IF version

Excel Formula Training

Download 100+ Important Excel Functions

Excel video training