INDEX/MATCH with an approximate MATCH
MATCH type 1 finds the band; INDEX returns its label from any column.
Task:
Exam marks map to grades by the band table in E2:F6, where each row gives a band's lowest mark. In C2:C7 return each student's grade, using INDEX and MATCH.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of INDEX MATCH
0/4The same formula in the other shapes it takes at work.
- INDEX-MATCH Combination
Use INDEX and MATCH functions together for flexible lookups.
CoreAdvanced - INDEX/MATCH to the left
Return a column that sits before the one you search — which VLOOKUP cannot do.
Advanced - INDEX/MATCH with MAX: who is top?
Find the highest figure, then the name beside it.
Advanced - INDEX/MATCH with an approximate MATCH
MATCH type 1 finds the band; INDEX returns its label from any column.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Mark | Grade | From mark | Grade | |
| 2 | Ola | 74 | 0 | Fail | ||
| 3 | Priyanka | 50 | 50 | Pass | ||
| 4 | Quentin | 49 | 60 | Merit | ||
| 5 | Rosa | 88 | 70 | Distinction | ||
| 6 | Sami | 61 | 85 | Distinction* | ||
| 7 | Tomas | 85 |
What this exercise teachesMay contain the answer
This is VLOOKUP's approximate match rebuilt from parts, and it is more flexible: the label column can be anywhere, and the band column does not have to be the first. 49 falls short of 50 and fails; 85 sits exactly on a boundary and takes the higher band.