In this lesson we will learn about HLOOKUP function in Excel. HLOOKUP allows us to search the approximate and exact matching, wildcards (* ?) for partial matches, and finds in horizontal ranges like from specific row in a table. Here the value will be retrieved from a table by matching on it’s first row.
=HLOOKUP (value, table, row_index, [range_lookup])
- value – The value you are looking for in the first row of the table
- table – The table from which to retrieve a value.
- row_index – The row index in the table from which to retrieve a value.
- range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.
Most of the time you need to look for an exact match when you use the HLOOKUP function in Excel. In our below example we have 2 tables. One contains the list of products and their amount per unit. Let’s say this is table A.
There is another table which contains the category and unit information for the list of products. Let’s say it’s table B.
Now we need the amount per unit value in table B. In column D we have used the HLOOKUP function to retrieve the amount per unit value for each product from table A. In cell D6 of table B the HLOOKUP function is mentioned as follows.
- Here A6 is the value we need to find. This will be changed as A7, A8 and so on, while dragging the formula for the following cells in column D of table B.
- The table from where the value needs to be retrieved is B1:G2. Here we used $(B$1:G$2) so that the table index won’t change while dragging the formula for the following cells in column D of table B.
- We need to retrieve the amount per unit which is in 2nd row of table A. Hence we put 2 in row index argument of HLOOKUP function.
- In last argument we put FALSE which denotes we need an exact match here
- The range_lookup can be provided in binary 1(TRUE) or 0(FALSE).
- range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.
- Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.
- If range_lookup is TRUE (the default setting), a non-exact match will cause the HLOOKUP function to match the nearest value in the table that is still less than value.
- When range_lookup is omitted, the HLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.
- If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, HLOOKUP may return an incorrect or unexpected value.
- If range_lookup is FALSE (require exact match), values in the first row of the table do not need to be sorted.