What is HLOOKUP in Google Sheets?
HLOOKUP is the horizontal cousin of VLOOKUP. It searches the first row of a range for a key and returns a value from a specified row below. Wide month grids and matrix headers occasionally still use it.
When to use it
Use HLOOKUP when keys live in a top header row and values sit in fixed rows beneath, such as metric rows by month column.
When to skip it
Skip HLOOKUP when data is row-oriented records. VLOOKUP, XLOOKUP, or INDEX MATCH fit normal tables better.
How it works
-
1
Place lookup value matching a top-row header.
-
2
Select table array with headers in row 1 of the range.
-
3
Set row_index_num for which row below the header to return.
-
4
Use FALSE for exact header match in most cases.
-
5
Lock array references when copying across a dashboard.
-
6
Consider TRANSPOSE plus VLOOKUP if the table is easier to reason about vertically.
Examples in Google Sheets
Month metric
=HLOOKUP("Mar", B1:M4, 3, FALSE) pulls row 3 metric under March column header.
Rate by year
=HLOOKUP(E1, Rates!A1:Z5, 4, FALSE) fetches a rate row under selected year header.
Wide budget grid
=HLOOKUP(B$1, Budget!B1:AA10, 6, FALSE) reads one department row under chosen month.
Better Sheets resources
Common mistakes
-
Key not in first row of the HLOOKUP range.
-
Row index not updated after inserting rows in the table block.
-
Using HLOOKUP on vertical customer lists oriented wrong.
-
Approximate match on unsorted header rows.
-
Confusing row index with column index from VLOOKUP habit.
Frequently asked questions
- HLOOKUP vs VLOOKUP?
- HLOOKUP scans top row. VLOOKUP scans left column.
- HLOOKUP vs XLOOKUP?
- XLOOKUP handles horizontal arrays with clearer arguments.
- Why #REF!?
- Row index larger than range height.
- Transpose trick?
- TRANSPOSE then VLOOKUP sometimes simplifies wide tables mentally.
- HLOOKUP exact match?
- Use FALSE for header text that must match exactly.
- Multiple header rows?
- HLOOKUP only searches first row of range. Restructure or use INDEX MATCH.
- HLOOKUP still used?
- Less common today but appears in legacy wide calendar models.
Related Tutorials
Watch how HLOOKUP works
Use Index Match when you want Vlookup
How do I reference a different spreadsheet in Apps Script?
Related blog posts
Guides that explain HLOOKUP in more depth.
Quick Google Sheets Tips
Boost productivity and streamline your workflow in Google Sheets with our quick tips and free tools. Breathe new life into your spreadsheets today.
Read post →How To Calculate ROAS in Google Sheets
Showing you two ways to use sheets to calculate Return on ad Spend. ROAS. One way with formulas and one way with Apps Script. Learn more at Better Sheets: https://Bettersheets.co
Read post →Five Frustrating Formulas and How to Conquer Them
Google Sheets formulas can be frustrating for a few reasons. First, you’re trying to remember what to use on the fly. Then you can't remember the name of a formula. If you do figure out the name, you can't remember the syntax. Then you don't know the proper way to write the data. FRUSTRATING!
Read post →Related terms
VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →XLOOKUP
XLOOKUP searches a lookup array for a key and returns a matching value from a return array. It handles left lookups, approximate match, and custom not-found text without INDEX/MATCH gymnastics.
Read guide →INDEX
INDEX fetches a value from a range by row and column number. Combined with MATCH it powers flexible lookups. Alone it picks nth items from lists, dynamic headers, or matrix intersections without VLOOKUP column limits.
Read guide →MATCH
MATCH searches a range for a value and returns its relative position. Feeding INDEX or OFFSET with MATCH replaces brittle column index math. Sorted approximate match modes support tier tables and date brackets.
Read guide →Done reading about HLOOKUP?
Membership unlocks 600+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.