HLOOKUP in Excel & Sheets
Learn HLOOKUP to search horizontally and return values. Complete guide with examples, syntax, and solutions for Excel and Google Sheets.
Excel
Google SheetsExcelGoogle Sheets=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Quick Answer
HLOOKUP function HLOOKUP function searches the first row of a table and returns a value from a specified row below in Excel and Google Sheets. Syntax: `=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Practical Examples
Basic Quarterly Revenue Lookup
Find specific quarter revenue from horizontal financial report
Product Price Lookup by Month
Retrieve product pricing for a specific month from time-series data
Dynamic Row Index with Cell Reference
Use cell references for both lookup value and row index
Approximate Match with Sorted Data
Find the appropriate tax bracket using approximate matching
Error Handling with IFERROR
Prevent errors when lookup values don't exist
Multi-Sheet Reference
Lookup data from a different worksheet
Nested HLOOKUP for Two-Dimensional Lookup
Combine HLOOKUP with MATCH for dynamic column selection
Common Errors and Solutions
HLOOKUP cannot find the lookup value
The lookup value doesn't exist in the first row of the table array, or there are spelling differences, extra spaces, or data type mismatches between the lookup value and the table data.
1. Verify the lookup value exists exactly as it appears in the first row 2. Check for leading/trailing spaces using the TRIM function: =HLOOKUP(TRIM(A1), range, row, FALSE) 3. Ensure data types match - numbers stored as text won't match actual numbers 4. Use approximate match (TRUE) if looking for ranges, but ensure the first row is sorted in ascending order 5. Wrap in IFERROR for graceful handling: =IFERROR(HLOOKUP(...), "Not Found")
Always use data validation for lookup values, standardize text entries (uppercase/lowercase), and implement error handling with IFERROR in production formulas.
Example:
HLOOKUP references invalid cells
The row_index_num is greater than the number of rows in the table_array, or the table_array range has been deleted or moved. This commonly occurs when rows are deleted from the source data or when the row_index_num references a cell that was deleted.
1. Count the number of rows in your table_array - row_index_num must be ≤ total rows 2. Check that your table_array range hasn't been modified or deleted 3. Use named ranges instead of direct cell references for stability 4. Verify the row_index_num cell reference is correct and points to a valid cell 5. Consider using ROWS function to make row_index_num dynamic: =HLOOKUP(A1, data, ROWS(data), FALSE)
Use structured references (Excel Tables) or named ranges to prevent #REF! errors when data is modified. Always validate that row_index_num is within bounds.
Example:
HLOOKUP receives incompatible data types
The row_index_num parameter is not a number, contains non-numeric characters, or is less than 1. This also occurs when the table_array is incorrectly specified or when text is used where numbers are expected.
1. Verify row_index_num is a positive number ≥ 1 2. Check that row_index_num doesn't reference a text cell 3. Ensure table_array is a proper range reference (A1:D10), not individual cells 4. If using a cell reference for row_index_num, use VALUE() to convert text to number 5. Verify the table_array contains the expected data types
Use data validation to ensure row_index_num cells contain only positive integers. Document expected data types in headers.
Example:
HLOOKUP returns unexpected values
Using TRUE for range_lookup (approximate match) when FALSE (exact match) is needed, or the first row is not sorted in ascending order when using approximate match. This can also occur when row_index_num points to the wrong row.
1. Use FALSE for exact matches: =HLOOKUP(value, table, row, FALSE) 2. If using approximate match (TRUE), ensure the first row is sorted in ascending order 3. Double-check the row_index_num - remember it's 1-based, not 0-based 4. Verify you're searching in the correct table_array 5. Check for hidden rows that might affect row counting
Default to FALSE for range_lookup unless specifically needing approximate matches. Add comments to formulas documenting the expected behavior.
Example:
Best Practices and Advanced Tips
Use Named Ranges for Maintainability
Instead of hardcoding cell ranges like A1:M10, create named ranges (e.g., 'QuarterlyData') for your table_array. This makes formulas more readable and prevents errors when data is moved or expanded. Named ranges automatically adjust when rows or columns are added.
Combine with MATCH for Dynamic Row Selection
Rather than hardcoding row numbers, use MATCH to dynamically determine which row to return. This creates flexible formulas that adapt when row order changes or new rows are added. The pattern HLOOKUP(value, table, MATCH(row_name, first_column, 0), FALSE) provides two-dimensional lookup capability.
Always Use FALSE for Exact Matches
Unless you specifically need range-based approximate matching (like tax brackets), always use FALSE as the fourth parameter. The default TRUE can return unexpected results if your data isn't perfectly sorted. Make FALSE your standard practice to avoid subtle bugs.
Be Aware of Performance with Large Datasets
HLOOKUP can be slow with very wide tables (hundreds of columns). For large datasets with frequent lookups, consider using INDEX/MATCH which often performs better, or restructure your data vertically to use VLOOKUP. If your table has more than 50 columns, benchmark performance.
HLOOKUP vs VLOOKUP: When to Choose
Use HLOOKUP when your data is naturally organized horizontally (time periods across columns, categories in rows). Use VLOOKUP when data is organized vertically (categories in columns, records in rows). If your data could work either way, vertical organization typically performs better and is more intuitive for most users.
Handle Multiple Matches with Helper Columns
HLOOKUP only returns the first match. If you need to return multiple matches, create helper columns that combine your lookup criteria with a counter (e.g., 'Q1-1', 'Q1-2') to make each lookup value unique, or use FILTER function in Excel 365.
Document Your Row Index Logic
Always add comments or documentation explaining what each row_index_num represents, especially in shared workbooks. This prevents confusion and errors when others modify your formulas. Consider creating a reference table mapping row numbers to their meanings.
Google Sheets Differences
HLOOKUP works identically in Google Sheets and Excel with one exception: Google Sheets is more forgiving with data types and will attempt automatic conversion. However, best practice is to explicitly ensure data types match regardless of platform.
Need Help with HLOOKUP in Excel & Sheets?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Master the powerful INDEX MATCH combination. Learn flexible lookups that surpass VLOOKUP with examples and error solutions for Excel & Sheets.
ExcelSheetsFind value positions in Excel/Sheets with MATCH. Learn syntax, examples, errors, and combine with INDEX for powerful lookups.
ExcelSheetsMaster the VLOOKUP function with practical examples and error solutions. Learn how to search and retrieve data efficiently in Excel and Google Sheets.
ExcelSheetsThe CHOOSE function returns a value from a list based on index number. Learn with practical examples and error solutions for Excel and Sheets.
ExcelSheets