Lookup FunctionsBeginner

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

7 rows × 5 columns2 cells you fill in
ABCDE
16-Month1-Year2-Year5-Year
2Standard CD2.12.853.053.4
3Jumbo CD2.353.13.33.65
4
5Term Requested1-Year
6Standard CD Rate
7Jumbo CD Rate
What this exercise teachesMay contain 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.