SheetsdrillHLOOKUP
HLOOKUP(search_key, range, index, [is_sorted]) searches the first row of a range for a value and returns the cell in the same column from the row number you give. It is VLOOKUP turned sideways, for tables whose headers run across the top such as months or regions.
Syntax
=HLOOKUP(search_key, range, index, [is_sorted])| Part | What it is |
|---|---|
search_key | The value to find in the first row, for example a month name. |
range | The table, with the row to search as its first row. |
index | The row of the range to return, counting the first row as 1. |
is_sorted | FALSE for exact match; TRUE or omitted assumes the first row is sorted and does approximate matching. |
Examples
=HLOOKUP("Mar", A1:M3, 2, FALSE)Result: The revenue figure in row 2 under the Mar column
=HLOOKUP(B10, A1:M3, 3, FALSE)Result: Row 3's value under whichever month is typed in B10
=HLOOKUP("West", B1:F13, MATCH("Mar", A1:A13, 0), FALSE)Result: The March value in the West column, with the row found by MATCH
Common mistakes
- Wrong results because the fourth argument was omitted; add FALSE unless the header row is sorted ascending.
- #N/A on a header that looks right is often a date header: a real date and the text Mar do not match, so compare against the same type.
- #REF! because index is larger than the number of rows in the range.
Practice HLOOKUP
These drills check your answer on live data, free, no account needed.
- Row and column lookupReturn the units for the product in H1 and the quarter in H2 from the table. Use INDEX with two MATCH functions. Answer in H3.
Questions
What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches the first column and returns from a column to the right; HLOOKUP searches the first row and returns from a row below.
Can XLOOKUP replace HLOOKUP?
Yes. XLOOKUP accepts rows as lookup and result ranges: =XLOOKUP("Mar", A1:M1, A2:M2).
Related
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
- INDEX and MATCHHow INDEX and MATCH work together in Google Sheets to look up values left or right, do two-way lookups by row and column, and find the last match.
- XLOOKUPHow XLOOKUP works in Google Sheets: look up in any direction, set a not-found value, do approximate matches and search from the bottom, with common errors.
- IFERRORHow IFERROR in Google Sheets hides errors like #N/A, #DIV/0! and #VALUE! with a blank, a zero or a message, with examples for VLOOKUP and division.