Home › Practice questions › HLOOKUP › Question 5

Excel HLOOKUP Practice Question: Find the tax rate for each income with an approximate match on the table A1:D2…

By Rinkesh Shah, founder of Cellgrids.

The question

Find the tax rate for each income with an approximate match on the table A1:D2 (the rate starts at the income in the top row). Put the results in B5 to B7.

The data

	A	B	C	D
1	0	250000	500000	1000000
2	0	0.1	0.2	0.3
3
4	Income	Rate
5	300000	?
6	750000	?
7	1200000	?

The yellow cells are where your answer goes.

Show answer

Expected results:

B5 = 0.1
B6 = 0.2
B7 = 0.3

How it works

Explanation: The approximate match picks the last income limit that is not above the income, and returns the rate under it.
Syntax: =HLOOKUP(A5,$A$1:$D$2,2,TRUE)
300000 is above 250000: 0.1. 750000 is above 500000: 0.2. 1200000 is above 1000000: 0.3.

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.