Hlookup in Excel



Hello all. Let’s look at Hlookup function. Hlookup is a function that looks up the specified value in horizontally sorted values and returns value from specific row in the table.

Syntax

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

Here are certain points to note before looking at examples:

  1. Lookup value must always be located in the first row of the table
  2. Data has to be organized horizontally
  3. Unlike Vlookup, the default range lookup is TRUE, which means non-exact match

Let’s look at a simple example. In the below data set, we have employee data organized in horizontal way (B2:F6). We have the lookup table (a.k.a output table) as J2:K6 – the lookup value would be Emp# and we need the asset type as output

Formula used here: =HLOOKUP(J3,B2:F6,5,0). In this formula, the formula would lookup 1001 (J3), the table would be the base employee data, it has to return the row#5 (asset type). The range lookup used here is “0” that would get the exact match

Please be careful about providing the row_index_number – if changed the value differs. Also, please ensure you don’t add or remove rows from the master data.

Hlookup also supports wildcard search. Let’s change the output array slightly. Let’s take the lookup data as Name and have wildcards in the name field. The same formula, but with change in table range and row_index_num, the result will be same.

Note, for Sac, we didn’t add * – this made the lookup value as missing value and hence returned NA error. Speaking of NA error, we can trap the error and customize the message using IFNA or IFERROR function. Shown below:

Let’s look at example of using “TRUE” or “1” as range lookup for approximate match. Note, to use approximate match, we need to sort our data. In the below data set, we have the sales figures made by employees between A6:B12. We have the company’s commission structure based on total sales (sorted horizontally and in ascending order) between A2:H3. We need to calculate the commission for each employee.

Formula used: =HLOOKUP(B7,A2:H3,2,1). When we use the range lookup as “TRUE” or “1” (approximate match), the function would take one step back for the previous available value and then return the appropriate match. In this example, the function looks “3749” in the table – the previous value for 3749 is 3500 in the table and returns 6. For the lookup value 807, the available value before 807 is 500 in the table and returns 2.

To re-iterate, Hlookup is only for values Horizontally and lookup being the first row of the table. If you have values vertically, you need to check Vlookup and if you don’t have tables that may not have the lookup value as first row always, check out Xlookup. Xlookup is currently available in Excel 365

Video Tutorial

I hope you like this tutorial. As always, please provide your valuable feedback. Thanks for reading.



Leave a Reply

%d bloggers like this: