Function Problems

How to Create an Excel Formula to Identify Performance Level by Score and Grade

Amos Gikunda Oct 16, 2026 868 views

Question details

The user needs an Excel formula to dynamically determine a student's performance level by matching their grade level and test score against specific numeric score bands.

How to Identify Performance Level by Score and Grade in Excel

Product: Excel
Device & OS: not provided
Scenario: Evaluating student performance by looking up their grade and test score in a two-dimensional threshold table.
Observed behavior: A dynamic formula is required to correctly identify and return the performance level category based on two distinct criteria.

Before you start

Ensure that your score thresholds in the lookup table are formatted as numbers (not text) and are sorted in ascending order to allow for accurate approximate matching.

Solution 1Recommended

Use INDEX, MATCH, and FILTER Formulas

This recommended formula combination efficiently filters the threshold data for a specific grade and finds the appropriate performance level score band.

By nesting the FILTER function inside the MATCH function, you can isolate the specific row corresponding to the student's grade. The MATCH function then finds where the student's score falls within that filtered array, and INDEX retrieves the column header (the performance level).

Format data as numbers

Select your score threshold cells, remove any text formatting, and ensure they are recognized as numeric values in Excel.

Sort the thresholds

Verify that the score bands in your source table are sorted in ascending order (e.g., from lowest score to highest score) from left to right.

Apply the combined formula

Select the target cell and enter the formula: =INDEX($B$1:$E$1,MATCH(C12,FILTER($B$2:$E$9,$A$2:$A$9=B12),1)). Adjust the cell references according to your actual data layout.

Copy the formula

Press Enter to calculate the result, then drag the fill handle down to apply this performance level formula to other students in the list.

Formula Breakdown: The '1' at the end of the MATCH function ensures an approximate match, which is critical for assigning scores that fall between the defined threshold numbers.

Solution 2

Use INDEX, MATCH, and OFFSET Formulas for Older Excel Versions

An alternative method if the FILTER function is not available in your version of Excel.

Advanced Spreadsheet Functions in WPS

Effortlessly Calculate Performance Levels in WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, and FILTER, allowing you to seamlessly evaluate student scores and automate complex grading rubrics.

1. Open your grading worksheet: Launch WPS Spreadsheet and open the document containing your student scores and grading thresholds.
2. Verify data formatting: Select the threshold data and confirm that the numbers are formatted as numeric values and sorted ascendingly.
3. Enter the lookup formula: Click on the target cell and input your INDEX and MATCH formula to locate the correct performance tier.
4. Apply to all students: Press Enter to execute the formula, then double-click the bottom-right corner of the cell to autofill the rest of the column.

Fully compatible with Microsoft Excel formulas, including array functionsAdvanced data processing capabilities for complex lookup scenariosFree to use with a lightweight, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MATCH function return a #N/A error?

This typically occurs if the score thresholds are stored as text instead of numbers, or if the exact grade level cannot be found in the source table. Ensure both your lookup values and table array share the exact same numeric formatting.

Why must the score thresholds be sorted in ascending order?

When using the MATCH function with a match_type of 1 (approximate match), the spreadsheet engine assumes the array is sorted in ascending order. If it is not, the formula cannot accurately identify which band the score falls into, leading to incorrect performance levels.

Can I use VLOOKUP instead of INDEX and MATCH for this task?

VLOOKUP is primarily designed for single-criteria vertical lookups. Because this scenario requires matching the grade vertically and then finding the score range horizontally, combining INDEX and MATCH with FILTER (or OFFSET) is required to handle the two-dimensional lookup.

Excel & Data Problems