Home › Practice questions › VLOOKUP › Question 4

Excel VLOOKUP Practice Question: Using VLOOKUP with approximate match, find each student's grade from the band…

By Rinkesh Shah, founder of Cellgrids.

The question

Using VLOOKUP with approximate match, find each student's grade from the band table in E2:F6 and put it in C2 to C5.

The data

	A	B	C	E	F
1	Student	Marks	Grade	From	Grade
2	Asha	82	?	0	F
3	Ben	67	?	50	D
4	Chitra	45	?	60	C
5	Dev	91	?	75	B
6				90	A

The yellow cells are where your answer goes.

Show answer

Expected results:

C2 = B
C3 = C
C4 = F
C5 = A

How it works

Explanation: With TRUE, VLOOKUP finds the largest value in the first column that is not above the lookup value. The band table must be sorted from small to large.
Syntax: =VLOOKUP(B2,$E$2:$F$6,2,TRUE)
82 falls in the band starting at 75, so B. 67 falls in the 60 band, so C. 45 falls in the 0 band, so F. 91 falls in the 90 band, so A.

Try it yourself in the live sheet

Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.

Try this question in the live sheet →

VLOOKUP syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Looks up a value in the first column of a range and returns a value from another column, same row. Pulling an employee's salary into another sheet using their ID. Full VLOOKUP guide.