In brief summary, the Excel HLOOKUP function looks for given criteria in the first row of a range, and once found, it looks in that column and at row ‘X’ in your range. It’s almost identical to VLOOKUP, but the difference being VLOOKUP is V for Vertical (Columns), and HLOOKUP is H for Horizontal (Rows).
Syntax Explained
=HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])
If you know VLOOKUPS, then you’ll grasp HLOOKUP in minutes. It all works the same; we’re just switching from searching left to right by columns (VLOOKUP) to searching top to bottom by rows (HLOOKUP).
Lookup_value
As per Excel, it defines this part of the syntax as “the value to be found in the first row of the table and can be a value, a reference, or a text string.” This is where you instruct the function to find criteria you define; it must appear in the first row; it can be a number, date, text, or a cell reference that contains data.
Table_array
Excel defines this as “a table of text, numbers, or logical values in which data is looked up. Table_array can be a reference to a range or a range name”. Basically, this is the range you select with your criteria appearing in the first row of your range and your result anywhere beneath that row but still within the range. The result you are looking for doesn’t have to be the last row.
Row_index_num
As per Excel, it defines this as “the row number in table_array from which the matching value should be returned. The first row of values in the table is row 1”. This will be a number of 2 and above, and it will determine where the function looks on row ‘x’ in your selected range.
Range_lookup
Excel defines this as “a logical value; to find the closest match in the top row (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE.” Basically, enter TRUE (or 1) for a close match or FALSE (or 0) for an exact match; mostly, you’ll use FALSE (I prefer typing a 0 to get the same result, it’s fewer characters, it all helps!)
Ok, let’s start with a very basic non-real-world example.
Examples
Basic HLOOKUP
Try replicating the below. Write our HLOOKUP function in cell B2 to check the range B2:E3 for the occurrence of the content found in cell B5 (in this case, it’s the letter A) in our first row of the range. We only have two rows in our range, so we want to find the result in the second row, 2 of the range, in this basic example.
B5lookup_valueB2:E3
In the function below is our table_array, the number 2 is the row_index_num, and finally, the range_lookup of 0 instructs the function to find an EXACT match.
You need to see below to demonstrate the difference in using 0 (EXACT) and 1 (CLOSEST MATCH). Same example below as above, only this time A, B, C, and D are now A, B, F, and X. Also, the 0 is now a 1 (CLOSEST MATCH).
In the example above, the lookup value is C and returns the eighth result. This is because the function is set to find a logical value and the closest match in the top row (sorted in ascending order). Have a play around with the example and try different letters; you’ll see the pattern.
The letter C comes after B; the result will stay at 8 if you continue to G, then switch to 10 once you hit F and beyond to W.
Simple? So that’s the basics out the way; let’s look at some other data and examples.
Sales Data Example
Ok, so sales data for the year, and you need to find out what the sales were for April 2017 for Client B. I’ve clicked in the fx bar (shown in the orange box) to highlight the selected cells in color.
Can you work out what the result is? The intersection of Apr-17 and Client B = 12; you got it.
Like with any lookup function to work properly, it MUST find a 100% match when using the 0 (EXACT MATCH) option. The Apr-17 date is displayed as such due to formatting, but closer inspections show this actually to be 01/04/2017.
It’s worth noting also Excel stores all dates as a 5-digit number; 01/04/2017 is also actually 42826. Our lookup value in B10 will return #N/A when entering 02/04/2017. However, you can still get your ’12’ result if you use 02/04/2017 and change the 0 to a 1 (CLOSEST MATCH)
The function will also fail if you ask it to return the result in row 10 of a range when you have selected only eight rows in your range, for example.
Combined with MATCH
Ok, so in the above, you’ll see we reference a cell B10 (Apr-17); we can also add formulas into any function to create dynamic criteria that alter based on a selection by a user.
We can upgrade this formula to combine it with MATCH (explained in detail in another article). The MATCH function is fairly straightforward, so bear with me if you aren’t familiar with it’s a close relative to VLOOKUP and HLOOKUP. Armed with these 3, you’ll be unstoppable!
I’ve added B10 to be a Client lookup, here we want to use the MATCH function to determine our row_index_num. Also, I’ve kept the range in line with the range of the HLOOKUP range; this will help with alignment and accuracy.
The MATCH function in B13 returns 5, so Client D is found on row 5 of our table_array. You can insert the MATCH function in B13 right into the HLOOKUP function, where it’s referencing the row_index_num.
So, in essence, you can get the same result of 79 (intersection of Apr-17 and Client D) in a few ways, by an integer, by cell reference, or by another function.
BASIC = HLOOKUP (B11, C3:N8, 5, 0)
INTERMEDIATE = HLOOKUP (B11, C3:N8, B13, 0) ß where B13 contains 5 and formulated as = MATCH (B10, B3:B8, 0)
ADVANCED = HLOOKUP (B11, C3:N8, MATCH (B10, B3:B8, 0), 0)
Now have a play around changing B10 from Client D to Client B and changing the months. These fields are manual, but nothing is stopping you from making these into a drop-down list in DATA VALIDATION in the menu.
