Home › Practice questions › HLOOKUP › Question 3

Excel HLOOKUP Practice Question: The table A1:D2 gives the grade from the marks (0 is D, 50 is C, 75 is B, 90…

By Rinkesh Shah, founder of Cellgrids.

The question

The table A1:D2 gives the grade from the marks (0 is D, 50 is C, 75 is B, 90 is A). Find the grade of each mark with an approximate match. Put the results in B5 to B7.

The data

	A	B	C	D
1	0	50	75	90
2	D	C	B	A
3
4	Marks	Grade
5	95	?
6	60	?
7	30	?

The yellow cells are where your answer goes.

Show answer

Expected results:

B5 = A
B6 = C
B7 = D

How it works

Explanation: With TRUE, HLOOKUP finds the largest value in the top row that is not above the marks. The top row must be sorted from small to large.
Syntax: =HLOOKUP(A5,$A$1:$D$2,2,TRUE)
95 is above 90: A. 60 is above 50 but below 75: C. 30 is only above 0: D.

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 →

HLOOKUP syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Like VLOOKUP but searches across the first row and pulls a value down a column. Pulling a specific month's figure from a report laid out horizontally instead of in columns. Full HLOOKUP guide.