Advance Excel (Hlookup)

HLOOKUP FUNCTION - INTRODUCTION

Like Vlookup function, Hlookup function is also used to find a particular value but with the help of row index number.

So here is the basic syntax is:
=HLookup(lookup_value, table_array, row_index_num, [range_lookup])


HLOOKUP FUNCTION COMPONENTS
1. Lookup_value: The 'lookup_value' is an address of that value, which you want to find in the 'table_array'

2. Table_array: The 'table_array' is an address of those data items, in which you want to perform the find task.

3. Row_index_num: The 'row_index_num' is a row number, in which you, actually want to perform the find task.

4. Range_lookup: This attribute contains the following two conditions.
True: The 'true' is used for 'Approximate match, and by default, the Hlookup function takes True, if you leave the 'Range_lookup' as blank.

False: Its used for the exact match.


Let's Start An example...
Step 1. Open the Microsoft Excel.

Step 2. Enter the following data items.

Shortcut keys, which can use in Hlookup and in other functions.
Tab: This key is used to complete a selected formula name.

Ctrl + A: This key is used to open selected formula GUI window.

F4: This key is used to convert a relative cell address (Such: A1, B3, so on) to Absolute cell address (Such: A$1$, B$1$, B$4$, so on) and vice verse.

Step 3. Now insert the Hlookup formula into the desired cell.

Step 4. After completing the function name, we can open its GUI interface by using 'Ctrl + A' shortcut key.

For example, after pressing 'Tab' key, the Hlookup function looks like...
=lookup(
Now here, we have to press the 'Ctrl+A' to open the GUI window.

Step 5. Now fill the following arguments, in Hlookup GUI window.

Lookup value: Insert the value address, which you want to find.

Table array: Insert address of table array.

Row index number: Insert the row index number.

Range lookup: Insert true for an approx match or false for an exact match.

HLOOKUP FUNCTION ACROSS WORKSHEET

We can also apply Hlookup function or formula across the worksheet with the help of define name tool.
Let's do an example...

Step 1. Select the 'Table array' contents.

Step 2. Click on the 'Formula' menu.

Step 3. Click on "Define name'

Step 4. Insert a name and click on 'Ok' button.

.
Step 5. Click on another sheet, where you want to insert the Hlookup formula.

Step 6. Now insert the Hlookup function, like the below example.
=HLOOKUP(C2,Zone,2,FALSE) 

Explanation: In the above formula, 'Hlookup' is formula name, 'C2' is lookup value address, 'Zone' is a name of table array's address and 'false' for the exact match.

Vlookup Function>>

HOME