HLOOKUP

How HLOOKUP works

HLOOKUP is VLOOKUP rotated 90 degrees. It searches the first ROW of a table from left to right, finds a match, then moves DOWN to return a value from whichever row you specify.

It's designed for data that flows horizontally: quarterly reports with periods (Q1, Q2, Q3, Q4) across the top, monthly dashboards, or grade scales running left to right. Whenever the category labels are arranged in a row instead of a column, HLOOKUP is the function to reach for.

The catch, same as VLOOKUP, is direction. HLOOKUP can only look DOWN from the first row. The lookup value must always live in the first row of your selected range. If the data is shaped differently you'll need INDEX MATCH or XLOOKUP.

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

Arguments

HLOOKUP basic: pull quarterly revenue by period

You're an FP&A analyst with a quarterly P&L laid out horizontally: Q1 through Q4 across the top, metrics stacked down the side. The CFO asks for Q3 revenue. HLOOKUP finds it in one formula.

=HLOOKUP(G2,$B$1:$E$5,2,FALSE)

• •G2 → the period you're looking up (e.g. "Q3")
• •$B$1:$E$5 → the quarterly P&L table (locked with $)
• •2 → return row 2 (the Revenue row)
• •FALSE → exact match only

HLOOKUP with IFERROR: handle missing periods gracefully

Your dashboard pulls revenue by period. But what happens when someone types "Sep" or "Q1" and your dataset only has Jan through Apr? Without IFERROR, your report fills with #N/A. Wrap every HLOOKUP in IFERROR.

=IFERROR(HLOOKUP(G2,$B$1:$E$5,2,FALSE),"Not found")

• •G2 → the period being looked up (e.g. "Mar")
• •$B$1:$E$5 → the dashboard data: periods in row 1, metrics in rows 2-5
• •If the period is found → returns the Revenue (row 2)
• •If the period is missing → returns "Not found" instead of #N/A

HLOOKUP across sheets: pulling data from the master dashboard

Your monthly summary lives on Sheet1. The master dashboard with all metrics across the period axis lives on Sheet2. Pulling the right cell across tabs is HLOOKUP plus the Sheet2! prefix.

=HLOOKUP(A2,Sheet2!$B$1:$G$3,2,FALSE)

• •A2 → the period on Sheet1 (e.g. "Mar")
• •Sheet2!$B$1:$G$3 → the master dashboard on Sheet2 (the prefix points to the other tab)
• •2 → return row 2 of Sheet2 (the Revenue row)
• •FALSE → exact match: period names must match exactly

Sheet1 B2 looks up "Mar" on Sheet2's header row and pulls back $4.8M from row 2 (Revenue). The Sheet2! prefix tells HLOOKUP exactly which tab to search. The rest of the formula works identically to a same-sheet lookup.

HLOOKUP with TRUE: assigning letter grades from scores

Grade scales. Tax brackets. Tier tables laid out left to right. These are the cases where HLOOKUP with TRUE earns its keep, when you want to find which RANGE a value falls into, not an exact match.

Critical requirement: your lookup row must be sorted in ascending order. If it is not, HLOOKUP returns garbage with no error message.

=HLOOKUP(H2,$B$1:$F$2,2,TRUE)

• •H2 → the student's raw score
• •$B$1:$F$2 → the grade scale (locked with $ since the same scale applies to every student)
• •2 → return row 2 of the grade scale (the letter grade)
• •TRUE → approximate match: pick the highest threshold ≤ the score

The $ sign mistake: dragging an HLOOKUP across columns

HLOOKUP's drag-direction problem is the mirror image of VLOOKUP's. When you drag an HLOOKUP formula to the RIGHT across columns, every column reference in the table_array shifts right by one column. If you didn't lock the table with $ signs, your lookup range slides off the data.

=HLOOKUP(B$1,$A$1:$G$8,2,FALSE)

• •B$1 → the month header above each column ($1 locks the row, but the column letter shifts as you drag right)
• •$A$1:$G$8 → the full dashboard (header + 7 metric rows × 6 months), fully anchored so it stays put no matter where the formula is dragged
• •2 → return row 2 (Revenue)
• •FALSE → exact match

Where people go wrong

1. 01
   Using TRUE instead of FALSE

   You leave the fourth argument blank or type TRUE. Excel quietly returns approximate-match results that look almost right but reference the wrong period. There's no error, no warning, just bad numbers in your report.

   Fix: Always explicitly type FALSE as the fourth argument unless you specifically need approximate matching (grade scales, tax brackets) and your lookup row is sorted ascending.
2. 02
   Forgetting $ signs when copying right

   You write a perfect HLOOKUP in cell B7 and drag it right to fill C7, D7, E7. By the last cell the range has shifted off the data and your cells fill with #N/A or wrong numbers. The table_array shifted right with each drag.

   Fix: Lock your table_array with $ signs: =HLOOKUP(B$1,$A$1:$E$5,2,FALSE). The $ before each column letter and row number makes the lookup table absolute.
3. 03
   Row index out of range

   You specify row_index_num as 5 but your table_array only has 4 rows. Excel returns #REF! and you wonder why a formula that looks correct is failing.

   Fix: Count the rows in your table_array carefully. If table_array is A1:E4 that is 4 rows, so row_index_num cannot exceed 4.
4. 04
   Lookup value not in first row

   Your headers are in row 2 and totals are in row 1 (a sub-header above the labels). HLOOKUP can only search the FIRST row of your selected range. It doesn't matter what's logically the header, only what comes first geographically.

   Fix: Adjust your table_array to start at the header row, e.g. A2:E5 instead of A1:E5. Or switch to XLOOKUP which lets you specify the lookup_array independently.

Notes

• HLOOKUP is case-insensitive. "Q1" and "q1" return the same result
• Wildcard characters (* and ?) work in lookup_value when range_lookup is FALSE
• For multiple matches HLOOKUP returns the first match only. It stops searching after the first hit (left to right)
• HLOOKUP is rarely used in modern spreadsheets. Most data is built vertically. Use it primarily on inherited reports or cross-tab summaries where the periods are already arranged across the top
• Row index counting starts at 1, not 0
• In Excel 365 and Excel 2021 and later use XLOOKUP instead. It works in any direction and has built-in error handling
• Approximate match (TRUE) requires the first row to be sorted ascending left-to-right. Unsorted data returns wrong results silently

What to learn next

Now prove it