How to Compare Two Assessment Scores and Return Student Names in Excel
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.

- 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.
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.
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.
Click on the top-left cell of your tracking grid where you want the first set of student names to appear.
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),""))
Press Enter to apply the formula and view the result for the first cell.
Click and drag the fill handle across the columns and down the rows to populate the entire comparison tracking grid.

Use a Dynamic Array Formula (LET and MAKEARRAY)
For advanced users, this dynamic variant generates the entire grid automatically without needing to manually copy or drag the formula.
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. Open Your File: Launch WPS Spreadsheet and open the document containing your student assessment data.
- 2. Locate the Grid: Navigate to your comparison grid sheet and click on the first intersecting cell.
- 3. Enter the Formula: Type in the TEXTJOIN and FILTER formula, ensuring the ranges correspond to your source columns.
- 4. Fill and Save: Press Enter, use the fill handle to apply the formula to the rest of the grid, and save your document.

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.




