How to calculate win lose points in Excel

In this article, we will learn How to calculate win-lose points using the VLOOKUP function in Excel.

Scenario:

Win or Lose are the results of a match played between two teams. Results produce gaining points for the winning team and reducing points for the losing team. There may be any number of outcomes of the match played. So Excel formula allows you to return any number of dependent results.

How to solve the problem?

If there is only two outcome of the match then you can choose the simple formula of IF function where the function checks for the result if win, the team gets 3 points or if lose, the team loses one point.

For this problem where we can have 4 or more than 4 results, We can use nested IF formula with 3 IF formula one inside the other. Learn more about nested IF function here. But this would require more effort and error chances are higher. So we started thinking if we had a result table and can extract values whenever and wherever required. VLOOKUP function can perform the task and the Generic formula is shared below. 

Generic formula:

=VLOOKUP(lookup_result, result_table, point_col, 0)

lookup_result : lookup value 

result_table : result table having results on left side and points on right side.

point_col : column number of the table to lookup points

0 : finds exact match

 

Example :

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we will apply the formula on the lookup cell value to get the corresponding result in the required cell. Below is the formula to apply. Here we have 4 corresponding values in the result table. 

Use the formula:

=VLOOKUP(D4,G4:H8,2,0)

Here look up value is given as cell reference and table is given as array reference. Press Enter to get the points for the win Draw result.

Now freeze the table array reference ($G$4:$H$8), to copy the formula to the remaining matches using the shortcut Ctrl + D or use the drag down cell option from the bottom right corner of the cell.

As you can see in the above image, all the points matching result value are here. You can also find out the marks or score of the test using the same formula. Learn more about extracting values from the table here.

Here are all the observational notes regarding using the formula. 

Notes:

  1. The VLOOKUP table must have the lookup_array in the leftmost column. 
  2. The col index cannot be 1.
  3. 0 argument is used for the exact match value. Use 1 for the approximate match value.
  4. Make sure to freeze the table array, if copying from one to the remaining cells.
  5. The function returns #N/A error, if look up value is not found in the lookup array. So catch the error, if necessary.
  6. You can also use the INDEX and MATCH function, if lookup point array is on the left of the lookup result array.

Hope this article about How to calculate win lose points in Excel is explanatory. Find more articles on look up  formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

Related Articles

How to use LOOKUP function in Excel : Find the lookup value in the array using the LOOKUP function explained with example.

Use INDEX and MATCH to Lookup Value : INDEX & MATCH function to look up value as required.

SUM range with INDEX in Excel : Use INDEX function to find the SUM of the values as required.

Popular Articles:

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.