Excel HLOOKUP function
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
|
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
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
