VLOOKUP approximate match practice exercises
With its last argument set to TRUE, VLOOKUP doesn't need an exact match: it finds the largest value in the first column that is less than or equal to what you're looking up. That makes it perfect for brackets like grades, tax bands and discount tiers.
Syntax
VLOOKUP(search_key, sorted_table, index, TRUE)
search_key- The value to place in a bracket, such as a score.
sorted_table- The bracket table. Its first column must be sorted from smallest to largest.
TRUE- Approximate match. Leaving the argument out also means TRUE.
The practice data
Test scores for 8 students, and a grade table (D1:E6) sorted from the lowest minimum score up. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Min score | Grade | |
| 2 | Ana | 92 | 0 | F | |
| 3 | Ben | 67 | 60 | D | |
| 4 | Cara | 85 | 70 | C | |
| 5 | Dev | 58 | 80 | B | |
| 6 | Eli | 74 | 90 | A | |
| 7 | Fay | 100 | |||
| 8 | Gus | 81 | |||
| 9 | Hal | 69 |
Worked example
=VLOOKUP(85,$D$2:$E$6,2,TRUE)
85 falls between 80 and 90, so VLOOKUP uses the 80 row and returns "B". Result:
| B |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the letter grade for each student's score using the grade table. Copied down to H9.
2 Return the grade for a score of 59.
3 Return the grade for Fay's score in B7.
Master VLOOKUP approximate match in the full course
Lesson 9 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon VLOOKUP approximate match mistakes
- An unsorted bracket table, which gives wrong answers without any error.
- Starting the table at a minimum above 0, so low scores return #N/A. Always include a 0 (or lowest possible) row.
- Using approximate match for names or IDs, where you need FALSE (exact).
FAQ
How do I do this with XLOOKUP?
Use match mode -1 (exact or next smaller): =XLOOKUP(B2,D2:D6,E2:E6,,-1). The table doesn't even need to be sorted.
Why not nested IFs?
A bracket table is easier to change: move a threshold in the table and every formula updates.