Excel HLOOKUP function

Summary

The HLOOKUP function in Excel is used for horizontal lookup. It searches for a specified value in the top row of a table or range and returns a value in the same column from a row you specify. HLOOKUP is particularly useful for retrieving data organized in rows, where the lookup value is located in the first row. It can perform both approximate and exact matches.
Syntax
				
					=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
				
			
  • lookup_value: The value to search for in the first row of the table_array.
  • table_array: The range of cells containing the data to be searched.
  • row_index_num: The row number in table_array from which to retrieve the value.
  • [range_lookup]: [Optional] A logical value: TRUE for an approximate match or FALSE for an exact match. If omitted, TRUE is the default.
Return value
Returns the corresponding value from the specified row, based on the match found in the first row of the table_array.

How to use

To use HLOOKUP, define the value to look up, specify the table array, and indicate which row to return the value from. Optionally, you can specify the match type – approximate or exact. Remember that HLOOKUP searches horizontally across the first row of the table_array.

Examples

HLOOKUP Approximate Match
Finding a Sales Level:
				
					=HLOOKUP(C5, table, 2, TRUE)

				
			
In a sales table (named “table”), this formula finds the best matching sales level for the amount in C5, returning the value from the second row.
HLOOKUP Exact Match
Matching a Numeric Rating:
				
					=HLOOKUP(C5, table, 2, FALSE)

				
			
In this example, the formula looks up a numeric rating (1-4) in C5 from a table named “table”, requiring an exact match, and returns the corresponding level from the second row.
HLOOKUP with Different Row Index
Retrieving Bonus Information:
				
					=HLOOKUP(C5, table, 3, TRUE)

				
			
Similar to the first example, but this time it retrieves the bonus from the third row of the “table” based on the sales amount in C5.
HLOOKUP for Non-Numeric Data
Lookup for Non-Numeric Categories:
				
					=HLOOKUP("Gold", table, 2, FALSE)

				
			
This formula looks up the category “Gold” in the first row of “table” and returns the associated value from the second row, requiring an exact match.

Additional Notes

  • HLOOKUP is less flexible than VLOOKUP as it requires the lookup value to be in the first row of the table_array.
  • For larger tables, consider using INDEX and MATCH as an alternative for more flexibility.
  • HLOOKUP works best with data organized horizontally. For vertical data, use VLOOKUP.

Related Functions

Excel XLOOKUP function

The Excel XLOOKUP function searches for a value, offering flexible matching options and improvements over VLOOKUP and HLOOKUP.

Excel XMATCH function

The Excel XMATCH function returns the position of a value within a range, offering advanced matching and search capabilities.

Excel VLOOKUP function

The Excel VLOOKUP function searches a table's first column for a value, returning a specified column's value from the same row.

Excel FILTER function

The Excel FILTER function dynamically isolates data in an array based on criteria, essential for targeted analysis and reporting.

Excel INDEX function

The Excel INDEX function retrieves values at a specific row and column in a range, key for dynamic data lookups and retrieval.

Excel MATCH function

The Excel MATCH function finds the position of a value within a range, essential for dynamic lookups and complex searches.

Content Navigation