HLOOKUP practice exercises
4 graded problems with realistic data, all medium.
VLOOKUP's sideways sibling: looks for a match across the top row of a table, then reads down. The 4 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the HLOOKUP guide first: syntax, a worked example and the mistakes people make.
In this set
- Look up a value laid out across columns
- Look up a value using a cell reference
- Pull a deeper row out of a sideways table
- See what HLOOKUP returns when nothing matches
The exercises
This table has quarters running across the top and revenue in the row below. Look up the revenue for "Q3".
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | Q4 | |
| 2 | 42000 | 48000 | 55000 | 51000 | |
| 3 | ? |
Enter a formula in A3.
Show a hint
HLOOKUP(lookup_value, table, row_index) works like VLOOKUP turned sideways; it searches the table's TOP row and returns from the row_index-th row down: =HLOOKUP("Q3",B1:E2,2).
Most tables run down (VLOOKUP's territory), but some, especially ones with time periods across the top, run sideways instead. HLOOKUP is the exact same idea as VLOOKUP, just rotated 90 degrees.
Look up the revenue for whichever quarter is typed into B4.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | Q4 | |
| 2 | 42000 | 48000 | 55000 | 51000 | |
| 3 | |||||
| 4 | Lookup quarter | Q4 | |||
| 5 | ? |
Enter a formula in A5.
Show a hint
Same shape, but the lookup value comes from a cell instead of being typed in: =HLOOKUP(B4,B1:E2,2).
A hardcoded lookup value only ever answers one question: pointing HLOOKUP at a cell is what makes the same formula answer a new question the moment someone changes that cell.
This table has three rows of data under the quarter headers: revenue, then cost, then profit. Look up the profit for "Q2" (the 3rd row of the table).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | Q4 | |
| 2 | 42000 | 48000 | 55000 | 51000 | |
| 3 | 30000 | 31000 | 33000 | 32000 | |
| 4 | 12000 | 17000 | 22000 | 19000 | |
| 5 | ? |
Enter a formula in A5.
Show a hint
Count rows the same way HLOOKUP's row_index always counts: the header row is row 1 of the table, so profit (3 rows below it) is row_index 4: =HLOOKUP("Q2",B1:E4,4).
This is the same off-by-one counting VLOOKUP teaches with columns: row_index 1 would just return the header you already searched on, so the data itself always starts at row_index 2.
Look up revenue for "Q5", a quarter that doesn't exist in this table, to see what HLOOKUP does when the lookup value isn't found.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | Q4 | |
| 2 | 42000 | 48000 | 55000 | 51000 | |
| 3 | ? |
Enter a formula in A3.
Show a hint
HLOOKUP returns the error text #N/A whenever the lookup value isn't found anywhere in the table's top row, exactly like VLOOKUP does: =HLOOKUP("Q5",B1:E2,2).
HLOOKUP fails the exact same way VLOOKUP does, which means IFERROR and ISERROR, already learned for VLOOKUP, work on HLOOKUP with zero changes.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the HLOOKUP exercises →