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)
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 
Excel formula: Map text to numbers
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...
Excel formula: Nested IF function example
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
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
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...

Related videos 
How to use VLOOKUP
In this video, learn how to use VLOOKUP to build a simple form to extract employee information from a table, in about 3 minutes. VLOOKUP is a key Excel function.
Why VLOOKUP is better than nested IFs
In this video, we look at a few reasons why VLOOKUP is a better option than nested IF statements.
How to replace nested IFs with VLOOKUP
In this short video, we look at how to replace a typical nested IF formula with a VLOOKUP formula. Compared to nested IF statements, VLOOKUP is simpler and more transparent. It's also easier to adjust later.
See also 
19 tips for nested IF formulas
23 things you should know about VLOOKUP

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

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.