HLOOKUP
Searches for a value in the top row of a table and returns a value in the same column from a specified row.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Searches for a value in the top row of a table and returns a value in the same column from a specified row.
HLOOKUP (Horizontal Lookup) searches for a specific value in the top row of a table or range and returns a value in the same column from a row you specify. It is the horizontal counterpart to VLOOKUP, ideal for data structured with headers in the first column rather than the first row. By default, the function performs an approximate match if the [ookup] argument is TRUE or omitted, which requires the first row to be sorted in ascending order. If [ookup] is FALSE, it performs an exact match and the data does not need to be sorted. Best practices include using absolute references (1:10) for the rray to prevent range shifting when copying formulas. Note that HLOOKUP is case-insensitive but supports wildcards like the asterisk (*) and question mark (?) for partial matches when performing exact searches. For more robust lookups that can search both vertically and horizontally without row index constraints, XLOOKUP is the modern recommended alternative.
Quick reference
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Inputs
Arguments
Example: A1
Example: A1:Z5
Example: 3
Example: FALSE
Formula patterns
Examples
Retrieve Monthly Sales Data
=HLOOKUP(B1, A4:M5, 2, FALSE)Find Tax Rate with Approximate Match
=HLOOKUP(C2, A10:G11, 2, TRUE)Partial Search using Wildcards
=HLOOKUP("Prod*", A1:Z5, 3, FALSE)Avoid these issues
Common Errors
#N/A
Cause: The lookup_value is not found in the first row of the table_array, or the lookup_value is smaller than the smallest value in the first row when [range_lookup] is TRUE.
Fix: Verify the lookup value exists in the top row. If using TRUE, ensure the row is sorted. For exact matches, ensure the fourth argument is FALSE.
#REF!
Cause: The row_index_num provided is greater than the total number of rows in the table_array.
Fix: Check the table_array range to ensure it encompasses the row you wish to retrieve data from, or reduce the row_index_num.
#VALUE!
Cause: The row_index_num is less than 1.
Fix: Ensure the row_index_num is at least 1, where 1 returns the value from the lookup row itself.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Searches for a value in the top row of a table and returns a value in the same column from a specified row.
alue: The value to find in the first row rray: The table to search in um: Row number to return (1=first row) ookup: TRUE=approximate match, FALSE=exact match
#N/A: Verify the lookup value exists in the top row. If using TRUE, ensure the row is sorted. For exact matches, ensure the fourth argument is FALSE. #REF!: Check the rray range to ensure it encompasses the row you wish to retrieve data from, or reduce the um. #VALUE!: Ensure the um is at least 1, where 1 returns the value from the lookup row itself.