Exploring Excel HLOOKUP Formula

USING HLOOKUP

HLOOKUP stands for Horizontal Lookup, and it searches for a value in the first row of a specified table or range. After finding a match, it returns the value from a specified row below the found value.

Formula:

=HLOOKUP(C13,D4:AD7,2,FALSE)

DEFINITIONS

G1 = You'd look for the District in the District row.

Once you find the District, you'd look to the cell below to find the Taluka.

Now, let's see how the =HLOOKUP(C13,D4:AD7,2,FALSE) formula does the same thing:

C13 = This is the District name you're looking for. It's like saying, "Find this District in the BOOK."

D4:AD7 = This is the range of cells where your BOOK is. It's like saying, "Look in these cells for the District and its corresponding Taluk."

2 = This tells Excel, "If you find the District name, look Second Row below the District to find the Taluk."

FALSE = This means you want an exact match. You only want the Taluk for the exact name you're looking for.

EXAMPLE : 1

"Look for the value in cell C13 within the range of cells D4 to AD7. Once you find it, give me the value that's two columns to the right."

=HLOOKUP(C13,D4:AD7,2,FALSE)

PICTURE 1

In above picture, details of District, Taluk and Sub-District Data are entered.

To extract TALUK by District Name, use the formula in Cell you want (Here it is C13):

The result is :

Check and Confirm the Result from PICTURE 1 above.

EXAMPLE : 2

"Look for the District value in cell I12 to N12 within the range of cells D4 to AD7. Once you find it, give me the value (Taluk) that's 2^ND row below."

=HLOOKUP(I12:N12,D4:AD7,2,FALSE)

In above picture, details of District, Taluk and Sub-District Data are entered.

The result is :

When you move the cursor to Bottom Right corner of the Cell, you will see fill handle (+ sign) as seen the picture below, Pls note, + looks smaller than this picture

Click, Hold and Drag till Cell N13. The cells shall be automatically updated with the Formula, as well as the Results.

RULES of HLOOKUP:

HLOOKUP is a powerful Excel function that allows you to search for a specific value in a table and return a corresponding value from a different column. To use it effectively, keep these rules in mind:  

Lookup Value Must Be in the First row:

The value you're searching for (your lookup value) must be in the first row of the table array. This is a crucial limitation of HLOOKUP. as it only searches horizontally across the first row and looks down to retrieve values from subsequent rows.

If you're using an approximate match (the fourth argument is TRUE or omitted), the data in the first row of the table array must be sorted in ascending order. This ensures that HLOOKUP can return the closest match.

Exact Match: Set the fourth argument (range_lookup) to FALSE or 0. This ensures that HLOOKUP will only return a value if it finds an exact match for the lookup value.

Approximate Match: Omit the fourth argument or set it to TRUE or 1. This allows HLOOKUP to find the closest match if an exact match is not available.

Row Index Number:

The third argument in the HLOOKUP formula specifies the row number from which you want to retrieve the value. The first row of the table array is considered row 1, the second row is row 2, and so on. Make sure you specify the correct row number to retrieve the value you need.

Definition of HLOOKUP arguments:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

lookup_value = This is the value you want to search for in the first row of the table array. It can be a number, text, or a cell reference.

table_array = This is the range of cells that contains the data you want to search. The lookup value must be in the first row of this range.

row_index_num = This is the column number in the table array from which you want to return a value. The first row is numbered 1.

[range_lookup]) (Optional) = within [square bracket] is optional. This argument specifies whether you want an exact match or an approximate match.

FALSE or 0 = Returns an exact match. The lookup value must be an exact match to the value in the first column of the table array.

TRUE or 1 = Returns an approximate match. The values in the first column of the table array must be sorted in ascending order. HLOOKUP will find the largest value that is less than or equal to the lookup value.