Win Loss Points Calculation Excel Formula

In the example shown, the formula in D5 is:

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: See this article for a detailed overview of nested IF formulas.

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.