Lookups
HLOOKUP function: look up values across horizontal column headers
Search the top row of a wide quarterly table for an exact period label and return the value from a specified row beneath it.
When periods run left-to-right across the top row
Financial budgets often place Q1, Q2, Q3, and Q4 across row 1 (B1:E1) with Freight in row 2 and Packaging in row 3. HLOOKUP scans row 1 horizontally and steps down by row_index_num.
| Line item (A) | Q1 (B) | Q2 (C) | Q3 (D) | Q4 (E) |
|---|---|---|---|---|
| Freight (row 2) | 410 | 465 | 520 | 490 |
| Packaging (row 3) | 120 | 135 | 150 | 140 |
Set the fourth argument to FALSE for exact header matches
In =HLOOKUP(G2, B1:E3, 2, FALSE), G2 holds "Q3", B1:E3 spans the header row plus two data rows, 2 selects the second row of B1:E3 (Freight = 520), and FALSE enforces an exact match. Changing row_index_num to 3 returns Packaging (150).
=HLOOKUP(G2,B1:E3,2,FALSE)Missing headers (#N/A) and row-insertion fragility
Querying an unlisted header like "Q5" returns #N/A, which you can catch with IFNA. Remember that inserting a new row inside B1:E3 shifts data rows without updating the hardcoded 2 or 3—switch to XLOOKUP when rows change frequently.
=IFNA(HLOOKUP(G2,B1:E3,2,FALSE),"Quarter not found")Try the example
Use the original values or change them to test your understanding.