Function Problems

How to Import Scores from Another Excel Sheet and Calculate Team Totals

Aamir Naveed AkramAamir Naveed Akram Oct 20, 2026 870 views

Question details

The user needs to retrieve individual player scores from a reference data sheet and automatically calculate combined team totals on a results sheet.

How to Import Scores from Another Excel Sheet and Calculate Team Totals
Product
Microsoft Excel
Device & OS
not provided
Scenario
Calculating total scores for paired teams by pulling individual member scores from a separate reference sheet.
Observed behavior
The user wants to input team member names and have Excel automatically fetch their respective scores from the DATA sheet, sum them up, and sort the total team scores.
Before you start

Ensure your DATA sheet contains exact name matches in the first column and their corresponding scores in the second column to avoid lookup errors.

Solution 1Recommended

Use VLOOKUP to Calculate Team Totals

The standard approach using the VLOOKUP function, compatible with all versions of Excel.

VLOOKUP searches for a specific value in the first column of a data range and returns a value in the same row from another column. By adding two VLOOKUP functions together, you can seamlessly combine the scores of two team members.

1
Set up your reference data

Verify that your DATA sheet contains member names in Column A and their scores in Column B.

2
Enter team names

On your RESULTS sheet, enter the names of the first team members into cells A2 and A3.

3
Apply the VLOOKUP formula

In the cell where you want the combined score (e.g., B2), enter the formula: =VLOOKUP(A2,DATA!A:B,2,FALSE)+VLOOKUP(A3,DATA!A:B,2,FALSE)

4
Calculate and sort

Press Enter to get the total score. Drag the fill handle down to apply the formula to other teams, then use the Sort feature under the Data tab to rank totals from largest to smallest.

Use VLOOKUP to Calculate Team Totals
Exact Match Requirement: The 'FALSE' argument in the VLOOKUP function ensures Excel only pulls the score if the name matches exactly, preventing accidental data mismatches.

Easily Calculate and Manage Team Scores with WPS Office

WPS Spreadsheet fully supports VLOOKUP, XLOOKUP, and other advanced formulas to help you manage data across multiple sheets effortlessly.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your DATA and RESULTS sheets.
  2. 2. Select the target cell: Go to the RESULTS sheet and click on the cell where you want the team's total score to appear.
  3. 3. Enter the formula: Type the formula =VLOOKUP(A2,DATA!A:B,2,FALSE)+VLOOKUP(A3,DATA!A:B,2,FALSE) and press Enter.
  4. 4. Apply to all teams: Drag the small square at the bottom-right of the cell to copy the calculation to all other teams in the list.
  5. 5. Sort the results: Navigate to the 'Data' tab and click 'Sort' to arrange the calculated team scores from highest to lowest.
100% compatible with Microsoft Excel (.xlsx) formatsSupports both VLOOKUP and XLOOKUP functions nativelyFree and lightweight alternative for seamless data managementBuilt-in sorting and filtering for quick team ranking
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return an #N/A error?

This usually happens if the name on the RESULTS sheet doesn't exactly match the name on the DATA sheet. Check for trailing spaces, typos, or hidden characters in both cells.

Can I calculate totals for teams with more than two members?

Yes. You can append additional VLOOKUP or XLOOKUP functions to your formula using the '+' sign for every extra team member cell.

How do I sort the calculated totals automatically?

While standard sorting is done manually via Data > Sort, you can use the SORT function wrapped around your array data in newer versions to dynamically sort the results based on the calculated column.

What is the advantage of using XLOOKUP over VLOOKUP?

XLOOKUP doesn't require column index numbers, defaults to an exact match, and is much more resilient. If you insert a new column into your DATA sheet, XLOOKUP will not break, whereas VLOOKUP's static index might.