How To Use the VLOOKUP and HLOOKUP Functions in Microsoft Excel

VLOOKUP is used to read the vertical table_array and HLOOKUP is used for the horizontal table.

Syntax:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Look_up_value can be a value, a reference, or a text string and used to match between table array and data.

Col_index_num is the column index number of table_array. The only difference between HLOOKUP and VLOOKUP is HLOOKUP use row_index_num (row index number of table_array).

Range_lookup is a logical value that specifies whether you want VLOOKUP to find the exact match or approximate match.

If TRUE, VLOOKUP will find an exact match or an approximate match. If an exact match is not found, then the next largest value that is less than lookup_value is returned.
If FALSE, VLOOKUP will find an exact match. If it's not found, the error value #N/A is returned.

Here, you will learn how to use VLOOKUP and HLOOKUP functions to get the value of base salary and transport fee for each employee (based on his group).

1. Syntax: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Look_up_value:
   In the screenshot, the fields "Group" are found in the table_array and data. So the first data of field "Group" (in B10) is the lookup_value for the first data of field "Base Salary", likewise for the next and last data.

   The range of table_array is $A$3:$C$6.

   Col_index_num of Group is 1, Col_index_num of Base Salary is 2, Col_index_num of Transport Fee is 3.

    

2. Formula of Base Salary data:
   · Put the cell pointer in D10.
   · Click Insert Function icon.
   · In Or Select a category section, select Lookup & Reference (see green rectangle in the screenshot below).
   · On Select a function, choose VLOOKUP (see yellow rectangle in screenshot below), then click OK.

   

3. In Function Arguments window:
   - Lookup Value section: (If you cannot see the first data of Group, just move/drag the Function Arguments window). Click the first data of Group in B10 (see blue rectangle in the screenshot below).
   - Table Array section, Select range of table_array: Click the first data of table in A3 then SHIFT+Click the last data of table in E6. Press F4 button to absolute the column and row of table_array (see purple rectangle in the screenshot).
   - Col_Index_Num section: type 2 (column index number of Base Salary is 2). See red rectangle in the screenshot.
   - Range_Lookup section, type: False (We will find an exact match. If you will find an approximate match, just type "TRUE" (see yellow rectangle in the screenshot), then click OK.
   · The result is 300.
   · Copy from D10 to D10......D14.

   

4. Formula of Transport Fee data:
   · Put the cell pointer in E10.
   · Click Insert Function icon.
   · Repeat step 3- 8 of base_salary's formula. Only in here, col_index_num, type 3.

   

5. Syntax: =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Look_up_value: In upper screenshot, the first data of field "Group" (in B7) is the lookup_value for the first data of field "Base Salary," likewise for the next and last data.

   The range of table_array is $B$1:$E$3.

   Row_index_num of Group is 1.
   Row_index_num of Base Salary is 2.
   Row_index_num of Transport fee is 3.

    

6. Formula of Base Salary data:
   - Put the cell pointer in D7.
   - Click Insert Function icon.
   In Or Select a category section, select Lookup & Reference. On Select a function, choose HLOOKUP.
   - Click OK.

   

7. In Function Arguments window:
   - Lookup_Value section, click the first data of Group in B7.
   - In Table Array section, select range of table_array: Click the first data of table in B1 then SHIFT+Click the last data of table in E3. Press F4 button to absolute the column and row of table_array.
   - Row_Index_Num section: type 2 (row index number of Base Salary is 2).
   - Range_lookup: type FALSE (We will find an exact match. If you will find an approximate match, just type "TRUE"), then click OK.
   - The result is 300. Copy from D7 to D7......D11.

   

8. Formula of Transport Fee data:
   - Put the cell pointer in E7.
   - Click Insert Function icon.
   - Repeat step 3-8 of base_salary's formula. Only in here, row_index_num: Type 3.

If you still don’t understand, you can delete the result data (base salary or transport fee) then re-enter again. Hope this helps!