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.
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.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 |
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.