Lookup › VLOOKUP37 / 67

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeVLOOKUP

VLOOKUP: convert scores to letter grades

A lecturer converts final scores into letter grades using the threshold table in columns E and F. Scores 0 to 49 = E, 50 to 59 = D, and so on.

Task

Fill C2:C6 with the letter grade for the score in column B, based on the threshold table E2:F6 (approximate match).

Cells to fillC2:C6

Required functionsVLOOKUP

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
VLOOKUP(value, table, column_number, TRUE) with TRUE finds the largest threshold that does not exceed the value.
Hint 2
The threshold table must be sorted ascending in its first column (E2:E6 already is).
Hint 3
Lock the table: $E$2:$F$6, then return column 2.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

C2:C6

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

Write it in C2, then fill down to C6.

Explanation

=VLOOKUP(B2,$E$2:$F$6,2,TRUE): for a score of 68, VLOOKUP stops at the 60 threshold (the largest one not above 68) and returns C. Approximate match suits range tables like this one; to find an exact code, use FALSE.

Starting worksheet: Sheet1
Row	A	B	C	E	F
1	Student	Score	Grade	Threshold	Grade
2	Arif	68		0	E
3	Bunga	85		50	D
4	Candra	49		60	C
5	Dinda	72		70	B
6	Erlang	50		85	A
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result