HLOOKUP function in Excel

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 HLOOKUPfunction 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 beA5=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.