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...

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.