Level 4 · Last year's quarter averages with HLOOKUP

Most tables run down the page: one record per row, one field per column. Some run sideways. Budgets, targets and finance summaries often put the periods across the top (Q1, Q2, Q3, Q4 or January to December) and the measures down the side. The summary in A1:E4 is one of those: quarters in row 1, then 2024’s completed orders, revenue and average order underneath.

HLOOKUP reads across

HLOOKUP is VLOOKUP turned on its side. It searches the top row of a table and returns a value from a row below it:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Argument	In D7	Means
lookup_value	B7	the quarter to find (“Q1”)
table_array	$B$1:$E$4	the summary, with the quarters in its first row
row_index_num	4	which row of the table to return, counting the top row as 1
range_lookup	FALSE	exact match only

For order 8250 in row 7, HLOOKUP finds “Q1” in B1 and goes down to the 4th row of the table: B4, 529.38. Use 3 instead and you get Q1’s total revenue, 348,329.76. Use 5 and you get #REF!, because the table has four rows. A quarter that isn’t in the top row, such as “Q5”, gives #N/A. As with VLOOKUP, write FALSE every time: the default is an approximate match.

Counting the row number

The row number counts inside the table, not on the sheet. The table starts at row 1 of the sheet here, so the numbers happen to agree; move the summary down to rows 10 to 13 and avg_order is still row 4 of the table. Leave column A out of the table: it holds labels, not quarters, and the lookup only needs B1:E4.

Comparing with IF

With last year’s average next to each order, the second column is a plain IF from chapter 1:

=IF(C7>=D7,"above","below")

Filled down to row 24, 4 of the 18 orders beat their quarter’s 2024 average: 9250 and 9500 in Q2, 10250 in Q3 and 11500 in Q4. Order 11750 is the near miss: 507.00 against Q4’s 508.75.

Sideways or flipped?

If you need to work with a horizontal table a lot, you can flip it: copy it, then Paste Special → Transpose (Ctrl+Alt+V in Excel, then tick Transpose; in Google Sheets, Paste special → Transposed). Rows become columns and VLOOKUP works on the copy. The copy doesn’t update when the original changes, though, so for a live summary, look it up where it is. XLOOKUP also reads rows: =XLOOKUP(B7,$B$1:$E$1,$B$4:$E$4) returns the same 529.38.

Common mistakes

• VLOOKUP on a horizontal table. It searches the first column, so it finds the wrong thing or nothing at all.
• The wrong row number. Row 3 is revenue, not the average.
• Unlocked table. B1:E4 filled down becomes B2:E5, and the quarters slide out of the top row.
• “Q1” vs “q1 ”. Lookups ignore case but not spaces. A stray space in an export makes every lookup on that row miss.