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

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