**HLOOKUP** will look for a value in the top row of a range and then returns a value in the same column from a row you specify in the table or array. Use **HLOOKUP** when you need to search values in columns. This is a great function and most commonly used . You will see in this post **HLOOKUP** function explained in detail.

**Syntax**

** **

**HLOOUP(lookup_value,table_array,row_index_num,range_lookup)**

**Lookup_value** is the value to be found in the first row of the table.

**Table_array** is a table of information in which data is looked up

**Row_index_num** is the row number in the table_array from which the matching value will be returned. If the **Row_index_num** is greater than the number of rows on table_array, **HLOOKUP** returns the #REF! value

**Range_lookup** is a logical value that specifies whether **HLOOKUP** to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned

**Example 1**

In the above illustration you have a range of values in cells A1:L2. In the cell A5 we need to get the value for a specific month in a particular column. So for easy reference the Month is given in Cell A4.The formula in the cell A5 will be **A5=HLOOKUP(A4,A1:L2,2,FALSE)**

**Lookup_value**

Where the first value A4 is the Lookup_value. Here we can also give the month name directly without giving the cell reference to A4. This can be done by updating the formula as** A5=HLOOKUP(“JAN”,A1:L2,2,FALSE)**

By giving the cell reference the advantage is that we are making the formula more interactive whereby we can just change the value of the cell A4 to FEB to get the FEB values without changing the values.

**Table_array**

The next part in the formula is “A1:L2″ which is the table_array which contains the base data. Here one important thing always to be noted is that your lookup_value should be always present in the TOP most row of the table array. Here we are going to search for the value Month and it should be the top row in the Table_array

**Row_index_num**

The third part is row_index_num which is 2. This denotes that the value we need to retrieve is residing in the second row in the table _array. To understand this in detail we will see one more illustration where we have different years in figures in each row in addition to the months

**Example 2**

We have updated the formula to include all the years in the table_array. Here the month reference is FEB and the row_index number is 3 which will retrieve the 2010 FEB value which is 2010.

**Range_lookup**

The last part is False which will give an exact match.You can also use 0 instead of False and 1 instead of True. See the following Example to understand this more.

**Example 3**

The above table shows the the discount availed at different levels of sales. Up to 1000 it is 0%, from 1000 to 1999 is 10% ,from 2000 to 2999 it is 20% and so on.

This can be worked out by giving the Range_Lookup value as false will give an approximate match which is exactly what we required in this scenario.

**Also see the tutorial explaining VLOOKUP function in Excel**

Hope this was some kind of use to you. Don’t forget to give your comments. Will be back soon with more advancedtutorial on **HLOOKUP** Function.

Nice Tutorial, Well Explained. Thanks a lot

Hi,

Its very Great. Good Explain. Try to publish new updates always.

its very nice tutorials. iam exited to watch new updates.

Dear all,

Thanks for explaining in easy steps, i have learned succussfully the hlook up command, but i need help in learning MACROs in detail.

Thanks

Fahad Rafi