Use Excel INDEX MATCH Logic to Assign Scores by Value Interval
Question details
Assign a score to each value in two separate datasets according to the lower and upper limits in corresponding grading intervals.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to look up a score from a grading table when a value falls above the interval's lower bound and at or below its upper bound.
- Observed behavior
- Each source value should return the score associated with the interval that contains it.
Arrange each grading table with a lower limit, an upper limit, and the score to return. Confirm that every source value is covered by one interval and that interval boundaries do not overlap.
Use INDEX and MATCH to return the score for each interval
Use this method when your grading table already contains lower and upper value limits and a score column for each range.
The multiplication of the two logical tests creates a 1 only for the row where the value is greater than the lower limit and less than or equal to the upper limit. INDEX then returns the score from that matching row.
In the first score-result cell, such as beside the value in J4, enter =INDEX($D$10:$D$15,MATCH(1,(J4>$B$10:$B$15)*(J4<=$C$10:$C$15),0)). This returns the score from D10:D15 when J4 falls within the limits in B10:C15.
In the score-result cell for the value in K4, enter =INDEX($D$10:$D$15,MATCH(1,(K4>$E$10:$E$15)*(K4<=$F$10:$F$15),0)). This uses the second set of lower and upper limits in E10:F15.
Select each formula cell and drag the fill handle down, or use Home > Fill > Down, to assign scores for the remaining rows.
In legacy Excel versions that do not support dynamic arrays, press Ctrl+Shift+Enter after entering the formula. In Microsoft 365 and current WPS Spreadsheets versions, press Enter normally.
Assign Interval-Based Scores in WPS Spreadsheets
WPS Spreadsheets lets you build interval score lookups with INDEX, MATCH, and logical comparisons, then fill the formula down for both datasets. Its familiar spreadsheet interface makes it easy to review ranges and grading boundaries.
- 1. Open the score workbook: Open the .xlsx or .xls file in WPS Spreadsheets and locate the source-value columns and grading tables.
- 2. Select the first score cell: Click the first output cell next to the first dataset, such as the row containing J4.
- 3. Enter the interval lookup formula: Type =INDEX($D$10:$D$15,MATCH(1,(J4>$B$10:$B$15)*(J4<=$C$10:$C$15),0)) in the formula bar and press Enter.
- 4. Add the second dataset formula: Click the output cell for the K4 dataset and enter =INDEX($D$10:$D$15,MATCH(1,(K4>$E$10:$E$15)*(K4<=$F$10:$F$15),0)).
- 5. Fill results down: Drag the fill handle from each result cell down through the remaining records to calculate all scores.
Frequently Asked Questions
Why does the interval formula use multiplication between the two conditions?
In Excel, TRUE*TRUE evaluates to 1. MATCH searches for that 1, so it finds the row where both conditions are met: the value is above the lower limit and no greater than the upper limit.
What happens if a value does not fit any grading interval?
MATCH returns a #N/A error when no row satisfies both tests. Check that the grading table covers the value and that the lower and upper limits are numeric.
Can I use IF and AND instead of INDEX and MATCH for score ranges?
Yes, but nested IF and AND formulas become difficult to maintain when there are many intervals. INDEX and MATCH is usually easier because you can edit the limits and scores directly in a table.
Do I need Ctrl+Shift+Enter for this formula?
Use Ctrl+Shift+Enter only in older Excel releases that require array-formula entry. Microsoft 365 and current WPS Spreadsheets versions normally calculate this formula after you press Enter.
