Function Problems

How to Compare Two Assessment Scores and Return Student Names in Excel

Huda QurayshiHuda Qurayshi Nov 11, 2026 871 views

Question details

The user needs a formula to extract and display student names in a comparison grid when their two assessment scores match specific row and column criteria.

Compare Two Assessment Scores to Return Student Names in a Grid
Product
Excel
Device & OS
not provided
Scenario
Building a tracking grid to compare student scores across two different assessments and automatically populating the intersecting cells with the corresponding student names.
Observed behavior
The goal is to filter a list of student names based on two score conditions and join multiple matching names into a single cell using a comma separator or a line break.
Before you start

Ensure your source data contains columns for student names and their respective assessment scores. Verify that you are using a version of Excel or WPS Office that supports dynamic array functions like FILTER and TEXTJOIN.

Solution 1Recommended

Use TEXTJOIN and FILTER for Standard Comparison

This is the most straightforward method to filter student names based on two conditions and combine them into a single cell.

This approach leverages the FILTER function to find all students matching both the row and column criteria. The TEXTJOIN function then merges these multiple results into one readable text string.

1
Select the Target Cell

Click on the top-left cell of your tracking grid where you want the first set of student names to appear.

2
Input the Formula

Enter the following formula: =TEXTJOIN(", ",TRUE,FILTER('Raw Data'!$A$2:$A$100,('Raw Data'!$B$2:$B$100=C$3)*('Raw Data'!$C$2:$C$100=$B4),""))

3
Apply the Formula

Press Enter to apply the formula and view the result for the first cell.

4
Fill the Grid

Click and drag the fill handle across the columns and down the rows to populate the entire comparison tracking grid.

Use TEXTJOIN and FILTER for Standard Comparison
Data Range Adjustment: Adjust the cell references in the formula to exactly match your raw data sheet. The asterisk (*) acts as an AND operator for the two assessment conditions.
Data Analysis Feature

Effortlessly Manage Student Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like FILTER and TEXTJOIN, making it perfectly suited for building dynamic assessment tracking grids without any hassle.

  1. 1. Open Your File: Launch WPS Spreadsheet and open the document containing your student assessment data.
  2. 2. Locate the Grid: Navigate to your comparison grid sheet and click on the first intersecting cell.
  3. 3. Enter the Formula: Type in the TEXTJOIN and FILTER formula, ensuring the ranges correspond to your source columns.
  4. 4. Fill and Save: Press Enter, use the fill handle to apply the formula to the rest of the grid, and save your document.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports dynamic arrays and advanced text functions for complex data filtering.Lightweight, fast, and features a familiar user interface for a seamless transition.Completely free to download and use for all your data management needs.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the FILTER function return an error when no students match the scores?

If no students match the criteria, the FILTER function can return a #CALC! or #N/A error. You can prevent this by including a third argument in the FILTER function, such as "" (an empty string), to display a blank cell instead.

Can I display the student names on separate lines instead of using a comma?

Yes. In the TEXTJOIN function, replace the comma delimiter ", " with CHAR(10). Ensure that you apply the 'Wrap Text' formatting to the cells so the names stack vertically on separate lines.

What if my version of Excel doesn't support the FILTER function?

If you are using an older version of Excel, you may need to use a complex array formula combining INDEX, SMALL, IF, and ROW. Alternatively, you can upgrade to a modern application like WPS Office, which natively supports dynamic arrays for free.