Excel HLOOKUP function

Description

Syntax and Arguments

Usage and Examples

• Example 1 – lookup in a table or a range
• Example 2 – lookup in an array

---

Description

The HLOOKUP function looks up a value in top row of a table or an array from left to right horizontally, then returns a value in the same column from the specified row. The HLOOKUP function can return approximate or exact matching, and use wildcards * or ? for finding partial matches.

Syntax and Arguments

Formula syntax

=HLOOKUP (value, table, row_index, [range_lookup])

Arguments

Value: Required, the look up value. Table: Required, the table or array you retrieve values from. Row_index: Required, the number of row that the matching value is in. Range_lookup: Optional. 1 means true, which return matching approximately; and 2 means false, which return matching exactly. Omitted is true.

Notes

1) When range_lookup is true, it returns the largest value which is less than look_value while there is no exact matching.

2) When range_lookup is true, the values in the top row of table_array must be placed in ascending order such as 1000,2000,3000…, or it returns #N/A error value.

3) If look_up value is smaller than the smallest value in top row of table_array, it returns #N/A error value.

Return Value

The HLOOKUP function returns a value in the same column from a row that you specify in the table or an array.

Usage and Examples

Example 1 – lookup in a table or a range

If Range_lookup is true:

=HLOOKUP(B5,$A$1:$E$2,2)

Or

=HLOOKUP(B5,$A$1:$E$2,2,1)

Or

=HLOOKUP(B5,$A$1:$E$2,2,TRUE)

It returns values matching approximately.

---

If Range_lookup is false:

=HLOOKUP(B5,$A$1:$E$2,2,0)

Or

=HLOOKUP(B5,$A$1:$E$2,2,FALSE)

It returns values exact matched or error values if there is no value exactly matched.

---

Example 2 – lookup in an array

Formula:

=HLOOKUP("c",{"a","b","c";12,13,14;"Nancy","Kate","Tom"},3)

Explain:

“c”: look_up value, non-numeric value needed to be enclosed with quotation marks.

{"a","b","c";12,13,14;"Nancy","Kate","Tom"}: array table you will lookup and retrieve values from, comma used as column separator, semicolon used as row separator.

3: row_index, the third row in the array table.

Return: Tom

---

Sample File

---

Excel Productivity Tools