**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 V**ertical (Columns) and HLOOKUP is **H for H**orizontal (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 number, date, text or a cell reference that contains data.

**Table_array**

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.

**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**

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.__Examples__

**How To Use The HLookup Function In Excel ****Basic HLOOKUP**

**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.

**B5**

**lookup_value**

**B2:E3**

**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.

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.

Can you work out what the result is? Intersection of **Apr-17** and **Client B = 12**, you got it.

**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**.

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.