What does it do?
In brief summary, the Excel HLOOKUP function looks for a 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).
=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).
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 number, date, text or a cell reference that contains data.
As per Excel, it 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.
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.
As per Excel, it defines this as “a logical value; to find the closet 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 less characters, it all helps!)
Ok, let’s start with a very basic non-real-world example.
How To Use The HLookup Function In Excel Basic HLOOKUP
Try replicating the below. Write our HLOOKUP function in cell B2 to check the range B2:E3 for the occurrence of 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 in this basic example the result we want to find is in the second row 2 of the range.B5lookup_valueB2:E3
To demonstrate the difference in using 0 (EXACT) and 1 (CLOSEST MATCH) see below. 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 8 result. This is because the function is set to find a logical value, and the closet 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.
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 colour.
Like with any look up 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 shows this to actually be 01/04/2017.
Can you work out what the result is? Intersection of Apr-17 and Client B = 12, you got it.
It’s worth noting also Excel store 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)
How To Use The HLookup Function In Excel ?The function will also fail if you ask it to return the result in row 10 of a range when you have selected only 8 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.
How To Use The HLookup Function In Excel ? We can upgrade this formula to combine it with MATCH (explained in detail in another article). The MATCH function is fairly straight forward so bear with me if you aren’t familiar, 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 its 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 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 with changing B10 from Client D to Client B, and change the months too. These fields are manual but there’re nothing stopping you making these into a drop-down list in DATA VALIDATION in the menu.