How to use the HLOOKUP Function with Examples

Hi Friends,

in today's discussion explains how to use the Excel HLOOKUP function with syntax and examples.

Free Download Sample Excel data file - HLOOKUP
YouTube Video Links :     HLOOKUP Basic Video 1     HLOOKUP Advanced Video 2

Description

The Microsoft Excel HLOOKUP function performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the row_index_number.

Syntax

The syntax for the HLOOKUP function in Microsoft Excel is:

HLOOKUP( lookup_value, table_array,row_row_ index_number,[range_lookup] )

Parameters or Arguments

lookup_value

The value to search for in the first row of the table.

table_array

Two or more rows of data that is sorted in ascending order.

row_index_number

The row number in table array from which the matching value must be returned. The first row is 1.

range_lookup

Optional. Enter FALSE or '0' to find an exact match. Enter TRUE or '1' to find an approximate match. If this parameter is omitted, TRUE is the default. Therefore, always mention it, make a good habit.

Note

• If you enter FALSE for the range lookup parameter and no exact match is found, then the HLOOKUP function will return #N/A.
• If you specify TRUE for the range lookup parameter and no exact match is found, then the next smaller value is returned.
• If row_ index_number is less than 1, the HLOOKUP function will return #VALUE!.
• If row_ index_number is greater than the number of columns in table, the HLOOKUP function will return #REF!.

Example

Let's look at some Excel HLOOKUP function examples and explore how to use the HLOOKUP function as a worksheet function in Microsoft Excel:

How to use HLOOKUP in Microsoft Excel

Based on the spreadsheet above, the following Excel HLOOKUP function will return the following for Employee Code E148 and the Name as

=HLOOKUP(X8,A:H,2,0)

Result : 'Xender' - and so on for the next cells if you drag the formula row wise.

 First Parameter

The first parameter in the HLOOKUP function is the lookup value to search for. So in this example, the HLOOKUP is searching for the value of  "Name" i.e., cell X8.

Second Parameter

The second parameter in the HLOOKUP function is the table which is set to the range of A:H. The HLOOKUP uses the first row in this range (ie: A1:H1) to search for the value of  "Name".

Third Parameter

The third parameter is the row_ index_number which is set to 2. This means that the second row in the table is where we will find the value to return. Since the table is set to A:H, the corresponding return value will be in A2:H2 (i.e., second row as specified by the row_ index_number of 2).

Fourth Parameter

Finally and most importantly is the fourth or last parameter in the HLOOKUP. In our example, it is set to FALSE or 0. This means that you need to find an EXACT match for the value of Name. We do not want to find a "close" match, but an EXACT match!! So if Name is not found in the range of A1:H1, then the HLOOKUP function should return #N/A.

Since the HLOOKUP is able to find the value of "Name" in the range A1:H1, it returns the corresponding value from A2:H2 which is 'Xender'.

Importance of Fourth Parameter - Range Lookup

Let's further explore the importance of specifying TRUE or 1 vs FALSE or 0 for the last parameter in the HLOOKUP function.

If you write '1' or TRUE in the range lookup field then it will search for approximate match of "lookup value".

Lets' change our example a little bit so we can visualize the difference.

I've made a little change in the example table as blow. Column headers' are replaced by numbers, and now I'll try to search for the numbers which are not in Exact Match but close to it i.e., Approximate Match.

How to use HLOOKUP in Microsoft Excel

As you can see I am asking HLOOKUP for the value of 190 which is not present in the table array, but table array shows two headers 188 & 200.

Now, 190 falls between 188 and 200, the HLOOKUP gives the next Lowest Value. But there is one Condition, the numbers in the arrary should be Sorted in ASCENDING ORDER.

For experimenting with HLOOKUP function, please download the file from the link provided and change the header value and you can see the difference very easily.

Absolute Referencing

Now it is important to mention one more mistake that is commonly made. When people use the HLOOKUP function, they commonly use relative referencing for the table range like we did in our examples above. This will return the right answer, but what happens when you copy the formula to another cell? The table range will be adjusted by Excel and change relative to where you paste the new formula.

So if you had the following formula in cell X9:

=HLOOKUP(X8,B:I,2,1)

And then you copied this formula from cell X9 to cell Y9, it would modify the HLOOKUP formula and the result will not be proper.

Therefore, please make your formula referencing absolutely by putting '$' (Dollar) sign or you can use 'F4' button, press this button after placing your cursor in the formula, it will change the cell range by printing a dollar sign.

Now the formula becomes, =HLOOKUP(X$8,$B:$I,2,1)

For more information about HLOOKUP please visit the YouTube Channel by clicking the link provided.