Home › Practice questions › HLOOKUP › Question 6

Excel HLOOKUP Practice Question: Look up the sales of each month in A1:E2. If the month is not in the table…

By Rinkesh Shah, founder of Cellgrids.

The question

Look up the sales of each month in A1:E2. If the month is not in the table write No data. Put the results in B5 to B7.

The data

	A	B	C	D	E
1	Jan	Feb	Mar	Apr	May
2	100	150	120	180	90
3
4	Month	Sales
5	Mar	?
6	Jun	?
7	Apr	?

The yellow cells are where your answer goes.

Show answer

Expected results:

B5 = 120
B6 = No data
B7 = 180

How it works

Explanation: HLOOKUP gives #N/A for a month that is not in the top row. IFERROR changes that into the text No data.
Syntax: =IFERROR(HLOOKUP(A5,$A$1:$E$2,2,FALSE),"No data")
Mar has 120. Jun is not in the table: No data. Apr has 180.

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.