Explanation
HLOOKUP is a powerful Excel function used to search for a value in a table or a range by matching it with data in a row. HLOOKUP is very similar to very popular function called VLOOKUP – While VLOOKUP searches vertically in columns, HLOOKUP searches horizontally in rows.
HLOOKUP is useful when the data is organized horizontally, with the lookup values in the first row and the corresponding data in rows below.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value – The value you are searching for, which can be text, number, or a cell reference.
- table_array – The range where you will perform the lookup. The range must start with the row containing the lookup value and should include the row from which you want to return the result.
- row_index_num – The row number from which you want to return the result. The number should be relative to the first row in the selected range in table_array.
- [range_lookup] – Which range lookup method should be used. In most cases we are using exact match and therefore if this is your first time using the function – we recommend using 0 (zero) or FALSE, both are for “Exact Match” – Go to the exact match to the value I’m looking for. 1 stands for “Approximate match”, and it should not be used on most cases so we’ll skip it for now.
Example
Here’s a quick example – let’s try to find out the age of the person with ID number
Please note that if we tried to return the Name row instead of Age row, we would not be able to do so with HLOOKUP unless we changed the position of rows. The reason for that is because row 1 is before row 2, which must be the first row in table_array as it contains the ID number. In such case, it is recommended to use INDEX MATCH instead, or even better – use the versatile XLOOKUP function.
Practice HLOOKUP
Here’s a small exercise that will show you how HLOOKUP is used:
Looking for more HLOOKUP exercises? Click here to visit our Exercises section!