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.

ABCDE
1StudentScoreMin scoreGrade
2Ana920F
3Ben6760D
4Cara8570C
5Dev5880B
6Eli7490A
7Fay100
8Gus81
9Hal69

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.

H2

2 Return the grade for a score of 59.

H2

3 Return the grade for Fay's score in B7.

H2

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 pricing

Common VLOOKUP approximate match mistakes

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.

Keep practicing