Read a CD rate off a table laid out by term, not by row
HLOOKUP matches a value across a header row and pulls back whatever sits a fixed number of rows beneath it — the mirror image of VLOOKUP, turned on its side.
Task:
You work the phones at a credit union, quoting certificate-of-deposit rates. The rate sheet runs term lengths across the top and lists two products underneath it — Standard and Jumbo. A customer has asked about the term sitting in B5. Pull the Standard CD rate into B6 and the Jumbo CD rate into B7.
Learning Objectives:
- Use HLOOKUP to match a value across a header row instead of down a first column
- Count row_index_num from the header row, not from the first row of data
- Recognize HLOOKUP as VLOOKUP turned on its side, for tables organized horizontally
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 6-Month | 1-Year | 2-Year | 5-Year | |
| 2 | Standard CD | 2.1 | 2.85 | 3.05 | 3.4 |
| 3 | Jumbo CD | 2.35 | 3.1 | 3.3 | 3.65 |
| 4 | |||||
| 5 | Term Requested | 1-Year | |||
| 6 | Standard CD Rate | ||||
| 7 | Jumbo CD Rate |
What this exercise teaches (contains the answer)
HLOOKUP scans the header row of $B$1:$E$3 for an exact match to the term in B5, then drops down row_index_num rows from the top of that range to read off a value — row 2 lands on Standard CD's row, giving 2.85 for a one-year term, and row 3 lands one row further, on Jumbo CD's 3.1. Counting from the header rather than from the first data row is the detail that trips people up: row_index_num=1 would hand back the header text itself, not a rate, because the header row is included in the count, not skipped. The only difference from VLOOKUP is which direction it searches — across the top instead of down the side — the counting-from-the-header behavior is identical either way.