How does HLOOKUP differ from VLOOKUP?

HLOOKUP searches horizontally across the top row of a range, while VLOOKUP searches vertically down the first column. Choose HLOOKUP when your keys are laid out in a row and you need to pull data from rows beneath them.

Can HLOOKUP perform a case-insensitive lookup?

Yes. HLOOKUP treats text lookup values case-insensitively by default, so "alice" and "Alice" are considered equal. If you need case-sensitivity, wrap the lookup value and the key row in EXACT or use FILTER with a comparison.

Why does HLOOKUP return #N/A even though the key appears to be in the table?

Most often this happens because range_lookup is FALSE and there is a hidden mismatch, such as extra spaces or the key being stored as text while lookup_value is numeric. Clean the data with TRIM or VALUE, or switch to an approximate match if appropriate.

When should I replace HLOOKUP with XLOOKUP?

XLOOKUP handles both vertical and horizontal lookups, offers exact-match default, and can return a custom value when not found. If you need these conveniences or want to avoid the requirement that the lookup row be sorted for approximate matches, XLOOKUP is the modern replacement.

HLOOKUP function

HLOOKUP searches the top row of a range for a key and returns a value from a specified row, supporting exact or approximate matches.

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Generate a HLOOKUP formula

Describe what you need. The generator will reach for HLOOKUP where HLOOKUP is the right tool, and tell you when it is not.

Arguments

How HLOOKUP reads its arguments

Argument	Required	Description
lookup_value	Required	The value to find in the first (top) row of table_array; can be a number, text, or logical value.
table_array	Required	A rectangular range whose first row contains the lookup keys; the function reads horizontally across this range.
row_index_num	Required	A positive integer indicating which row (relative to the top row) to return; 1 returns the key row itself, larger numbers return data rows.
range_lookup	Optional	Optional logical flag; FALSE forces an exact match, TRUE (or omitted) allows an approximate match on a sorted key row.

Returns

It returns a single scalar value from the intersecting cell.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Retrieve an employee's salary by ID

EmpID	101	102	103	104	105
Name	Alice	Bob	Carol	David	Eve
Dept	Sales	Engineering	HR	Engineering	Sales
Salary	72000	95000	68000	105000	73000
Status	Active	Active	On Leave	Active	Terminated

=HLOOKUP(104, A1:F5, 4, FALSE)

Result: 105000

The formula looks across the first row for the exact key 104. Because range_lookup is FALSE, only an exact match is accepted. The key is found in column E, and row_index_num = 4 points to the "Salary" row, so the intersecting cell contains 105000, which is returned.

2. Find the department of an employee using an approximate match

EmpID	101	102	103	104	105
Name	Alice	Bob	Carol	David	Eve
Dept	Sales	Engineering	HR	Engineering	Sales
Salary	72000	95000	68000	105000	73000
Status	Active	Active	On Leave	Active	Terminated

=HLOOKUP(103, A1:F5, 3, TRUE)

Result: HR

With range_lookup set to TRUE, HLOOKUP performs an approximate search assuming the top row is sorted ascending (which it is). The lookup value 103 matches the key in column D exactly, so the function returns the value from the third row – the "Dept" row – which is "HR".

3. Return an employee's status when the ID is missing

EmpID	101	102	103	104	105
Name	Alice	Bob	Carol	David	Eve
Dept	Sales	Engineering	HR	Engineering	Sales
Salary	72000	95000	68000	105000	73000
Status	Active	Active	On Leave	Active	Terminated

=HLOOKUP(106, A1:F5, 5, FALSE)

Result: #N/A

The lookup value 106 does not exist in the top row. Because range_lookup is FALSE, HLOOKUP requires an exact match; when none is found it returns the #N/A error, indicating that the key could not be located.

Common errors

Which HLOOKUP error are you seeing?

Error	Why it happens	How to fix it
#N/A	The lookup_value is not present in the first row when range_lookup is FALSE, or it is smaller than the smallest key with an approximate search.	Verify the key exists in the top row or switch to TRUE for an approximate match if the data is sorted.
#REF!	row_index_num is less than 1 or greater than the number of rows in table_array.	Adjust row_index_num to a value between 1 and the total row count of the table (e.g., use 4 for the Salary row in a 5-row table).
#VALUE!	row_index_num is not a numeric value (e.g., text) or range_lookup is not TRUE/FALSE.	Provide a numeric row_index_num and use TRUE or FALSE (or omit) for range_lookup.

Tips and when to use something else

Use FALSE for range_lookup when you need a guaranteed exact match; TRUE can return the nearest smaller key, which may be unexpected.
If your data is organized vertically (keys in the first column), consider VLOOKUP or the more flexible XLOOKUP instead of HLOOKUP.
Combine HLOOKUP with IFERROR to display a friendly message instead of #N/A, e.g., =IFERROR(HLOOKUP(...), "Not found").
When the lookup row contains mixed data types, ensure lookup_value matches the type (text vs number) to avoid false negatives.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.LOOKUPLOOKUP searches a one-dimensional range for a value and returns the corresponding entry from a result range, or the last value if no exact match is found.

Frequently asked questions

How does HLOOKUP differ from VLOOKUP?: HLOOKUP searches horizontally across the top row of a range, while VLOOKUP searches vertically down the first column. Choose HLOOKUP when your keys are laid out in a row and you need to pull data from rows beneath them.
Can HLOOKUP perform a case-insensitive lookup?: Yes. HLOOKUP treats text lookup values case-insensitively by default, so "alice" and "Alice" are considered equal. If you need case-sensitivity, wrap the lookup value and the key row in EXACT or use FILTER with a comparison.
Why does HLOOKUP return #N/A even though the key appears to be in the table?: Most often this happens because range_lookup is FALSE and there is a hidden mismatch, such as extra spaces or the key being stored as text while lookup_value is numeric. Clean the data with TRIM or VALUE, or switch to an approximate match if appropriate.
When should I replace HLOOKUP with XLOOKUP?: XLOOKUP handles both vertical and horizontal lookups, offers exact-match default, and can return a custom value when not found. If you need these conveniences or want to avoid the requirement that the lookup row be sorted for approximate matches, XLOOKUP is the modern replacement.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17