How to Use HLOOKUP in Google Sheets
=HLOOKUP(search_key, range, index, [is_sorted]). Use FALSE for an exact match.
What HLOOKUP does
HLOOKUP is VLOOKUP turned on its side. When your headers run across the top (months in a row, for example), HLOOKUP looks for a header in the first row and returns a value from the column where it finds it.
HLOOKUP syntax
=HLOOKUP(search_key, range, index, [is_sorted])
search_key: the value to find, such as"Feb".range: the range to search. Its first row is scanned for the key.index: the row number to return from, where the first row of the range is 1. An index outside the range returns #VALUE!.is_sorted(optional, defaultTRUE):FALSEreturns only an exact match.TRUEreturns the nearest value that is less than or equal to the key.
A step-by-step example
Here are monthly figures in cells A1:D3, with the months across row 1.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Sales | 120 | 150 | 90 |
| 3 | Costs | 80 | 100 | 70 |
To find the sales for February:
=HLOOKUP("Feb", A1:D3, 2, FALSE)
This returns 150. Here is why:
- Sheets scans the first row of
A1:D3and finds Feb in column C. - The index is
2, so it returns the value from the second row of that column: 150.
Row 1 of the range is index 1, Sales is index 2 and Costs is index 3.
Always set is_sorted to FALSE for exact lookups
Because the default is TRUE, leaving the last argument out makes HLOOKUP assume a sorted row and return the nearest smaller match. For text headers like months that gives wrong answers with no warning. Google's help page also notes that wildcards and regular expressions are not supported, and that with FALSE and repeated keys, the first match is used.
Common HLOOKUP errors
- #N/A. With FALSE, the key is not in the first row. Check spelling and hidden spaces.
- #VALUE!. The index is larger than the number of rows in the range.
- Searching a row that is not first. The key must be in the first row of the range you give. Start the range on the header row.
- Data in columns, not rows. If the headers run down the first column, use VLOOKUP instead.
Try it yourself
Use the monthly table above (A1:D3). Type your formula into the box, starting with =, then press Check.
Exercise 1: Sales for February
Write a formula that returns the Sales figure for Feb from A1:D3, using an exact match.
Exercise 2: Costs for March
Write a formula that returns the Costs figure for Mar from A1:D3, using an exact match.
What to learn next
- VLOOKUP: fetch one value from a table.
- XLOOKUP: look up a value in any direction.
- INDEX and MATCH: look up a value in any direction.
- MATCH: find the position of a value.
Syntax and rules in this guide were checked against Google's HLOOKUP help page. See all guides.