HLOOKUP function in Excel explained in detail

April 11th, 2011 by Tips Master | Posted under Excel Functions.

HLOOKUP FunctionHLOOKUP 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

 

HLOOKUP Function in Excel

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.

 

HLOOKUP in Excel

 

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

 

HLOOKUP Function in Excel Explained

 

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

 

HLOOKUP Function Example

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.

 

Comments

5 Responses to “HLOOKUP function in Excel explained in detail”
  1. Mike says:

    Nice Tutorial, Well Explained. Thanks a lot

  2. N.Vadhi Raju says:

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

  3. RajKumar says:

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

  4. Fahad says:

    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

Do you have any comments on HLOOKUP function in Excel explained in detail ?