Excel HLOOKUP Function

last modified October 5, 2026

The HLOOKUP function is a powerful lookup tool in Excel that searches for a value in the top row of a table and returns a value from a specified row. This tutorial provides a comprehensive guide to using the HLOOKUP function with detailed examples. You'll learn basic syntax, practical applications, and advanced techniques to master this essential Excel function.

HLOOKUP Function Basics

The HLOOKUP function performs a horizontal lookup by searching for a value in the top row of a table and returning a value from a specified row. It's ideal for data arranged horizontally.

Component	Description
Function Name	HLOOKUP
Syntax	=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Arguments	4 parameters (last optional)
Return Value	Matched value from specified row

This table breaks down the essential components of the HLOOKUP function. It shows the function name, syntax format, argument details, and return value characteristics.

The four arguments control what is searched, where it is searched, which row is returned, and how strict the match must be.

Argument	Meaning
lookup_value	The value to find in the first row of the table. It can be a number, text, a logical value, or a cell reference.
table_array	The range or array that holds the data. HLOOKUP always searches the first row of this range and reads results from the rows below it.
row_index_num	The row within table_array to return. 1 returns the first row, 2 the second, and so on.
range_lookup	Optional. FALSE requests an exact match, TRUE or omitted requests an approximate match.

The key difference from VLOOKUP is direction. HLOOKUP searches across the first row and returns a value from a lower row, while VLOOKUP searches down the first column and returns a value from a column to the right. Use HLOOKUP when your comparison values run horizontally across the top of a table.

Exact Match vs Approximate Match

The range_lookup argument decides how HLOOKUP interprets a miss. The two modes behave very differently.

range_lookup	Match type	Behaviour
FALSE	Exact match	Returns the value whose first-row entry equals the lookup value. If none is found, returns #N/A. Text comparison is case-insensitive, and wildcards are allowed.
TRUE or omitted	Approximate match	Returns the value for the largest first-row entry that is less than or equal to the lookup value. The first row must be sorted in ascending order, otherwise the result may be wrong.

With approximate matching, if the lookup value is smaller than the smallest entry in the first row, HLOOKUP returns #N/A. Because omitting the argument triggers approximate matching, always pass FALSE when you need a reliable exact lookup on unsorted data.

Basic HLOOKUP Example

This example demonstrates the simplest use of HLOOKUP to find a product price in a horizontal table.

A	B	C	D
Product	Apple	Banana	Orange
Price	1.25	0.75	1.10
			=HLOOKUP("Banana",A1:D2,2,FALSE)

The table shows product names in the first row and prices in the second row. The HLOOKUP formula searches for "Banana" and returns its price from row 2.

=HLOOKUP("Banana",A1:D2,2,FALSE)

This formula searches for "Banana" in the top row of A1:D2 and returns the value from row 2 in the same column. The FALSE parameter ensures exact match. The result will be 0.75.

HLOOKUP with Cell Reference

A more practical use of HLOOKUP involves using cell references for the lookup value. This makes the formula dynamic and reusable.

A	B	C	D
Month	Jan	Feb	Mar
Sales	1200	1500	1800
Lookup	Feb	=HLOOKUP(B3,A1:D2,2,FALSE)

This table shows monthly sales data arranged horizontally. The lookup value sits in cell B3, and the HLOOKUP formula in C3 references it instead of hard-coding the text.

=HLOOKUP(B3,A1:D2,2,FALSE)

The formula reads "Feb" from B3, finds it in the top row of A1:D2, and returns the sales figure from row 2. The result is 1500. Because the lookup value comes from a cell, changing B3 updates the result automatically.

HLOOKUP with Approximate Match

HLOOKUP can perform approximate matches when the last argument is TRUE or omitted. This is useful for finding ranges or graded data.

A	B	C	D
Score	60	70	80
Grade	D	C	B
			=HLOOKUP(65,A1:D2,2,TRUE)

This table shows score ranges and corresponding grades. The HLOOKUP formula finds the appropriate grade for a score of 65 using approximate matching.

=HLOOKUP(65,A1:D2,2,TRUE)

The formula searches for 65 in the top row and returns the grade from row 2. There is no exact match, so the largest first-row value that is less than or equal to 65 is used, which is 60, and the matching grade "D" is returned. The first row must be sorted in ascending order for approximate matches to work correctly.

HLOOKUP with Wildcards

HLOOKUP supports wildcards (* and ?) for partial matches when using exact match mode (FALSE). This example demonstrates this feature.

A	B	C	D
Code	APP123	BAN456	ORA789
Category	Fruit	Fruit	Fruit
			=HLOOKUP("BAN*",A1:D2,2,FALSE)

This table shows product codes and categories. The HLOOKUP formula uses a wildcard to find a partial match in the code.

=HLOOKUP("BAN*",A1:D2,2,FALSE)

The formula searches for any code starting with "BAN" and returns the category from row 2. The asterisk (*) represents any sequence of characters. The result is "Fruit".

HLOOKUP with Multiple Rows

HLOOKUP can retrieve data from any row in the table array, not just the second row. This example shows how to access data from the third row.

A	B	C	D
Employee	John	Sarah	Mike
Department	Sales	HR	IT
Salary	5000	4500	5200
			=HLOOKUP("Sarah",A1:D3,3,FALSE)

This table contains employee data with multiple rows of information. The HLOOKUP formula retrieves salary information from the third row.

=HLOOKUP("Sarah",A1:D3,3,FALSE)

The formula searches for "Sarah" in the top row and returns the salary from row 3. The result is 4500. The row_index_num parameter determines which row's data to return.

HLOOKUP Error Handling

HLOOKUP returns #N/A if the lookup value isn't found. This example shows how to handle such errors gracefully using IFERROR.

A	B	C	D
Product	Widget	Gadget	Thingy
Price	10.99	15.50	8.75
			=IFERROR(HLOOKUP("Doodad",A1:D2,2,FALSE),"Not Found")

This table demonstrates error handling when a lookup value doesn't exist in the table. The IFERROR function provides a user-friendly message instead of #N/A.

=IFERROR(HLOOKUP("Doodad",A1:D2,2,FALSE),"Not Found")

The formula attempts to find "Doodad" in the table. When not found, instead of #N/A, it displays "Not Found". This makes spreadsheets more user-friendly when dealing with potential missing data.

HLOOKUP vs VLOOKUP vs XLOOKUP

HLOOKUP is one of three lookup functions Excel offers. Knowing when to use each one avoids awkward data reshaping.

Function	Search direction	Returns	Notes
HLOOKUP	Across the first row	A value from a row below	Best for data laid out horizontally; approximate match by default.
VLOOKUP	Down the first column	A value from a column to the right	Best for data laid out vertically; approximate match by default.
XLOOKUP	Any direction	Any value in a return array	Exact match by default, can search left or up, and has a built-in not-found argument. Available in Microsoft 365 and Excel 2021 and later.

For example, the same result as =HLOOKUP("Banana",A1:D2,2,FALSE) can be written as =XLOOKUP("Banana",A1:D1,A2:D2). XLOOKUP is more flexible because it does not depend on the lookup row being the first row of the range, and it returns an exact match unless you ask otherwise.

HLOOKUP Errors and Troubleshooting

Cause	Symptom	Fix
Lookup value is not in the first row	#N/A error	Check spelling, use wildcards for partial text, or wrap in IFERROR
Approximate match on unsorted data	A wrong but plausible value	Sort the first row ascending, or use FALSE for an exact match
Lookup value is smaller than the smallest first-row entry	#N/A error with approximate match	Confirm the first row is sorted and the value is within range
row_index_num is less than 1	#VALUE! error	Use a row number of 1 or greater
row_index_num is greater than the number of rows	#REF! error	Make sure the index does not exceed the height of table_array
Wildcard characters used with approximate match	Literal match fails or wrong value	Wildcards only work when range_lookup is FALSE

The HLOOKUP function is essential for working with horizontally structured data in Excel. From simple exact lookups to approximate matches, wildcards, and robust error handling, it provides dependable data retrieval for tables that run across a sheet. Remember that it searches only the first row of the range, that exact matching is case-insensitive and supports wildcards, and that approximate matching demands a sorted first row. When you need to search in any direction or want exact matching by default, reach for XLOOKUP; otherwise HLOOKUP remains a compact and reliable tool for horizontal tables.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.