Easy steps to use Vlookup in Excel

Vlookup function remains one of the most widely used function in Microsoft excel. The function helps to bring data from another table to primary table, or lookup data from secondary table to primary table. This function works by looking up based on an ID or a primary key. This articles discusses easy steps to use Vlookup in Excel with an example. Vlookup syntax has 4 elements, the below Vlookup in Excel example discusses the same in detail.

Vlookup in Excel Example

John has Data of few employees for his company situated across various location globally. He has information like Employee ID, Name, Gender, City listed in one table. John also has salary information available for each of the Employee ID in another table. John needs salary data to be present in the primary table, so that he can do some detailed analysis. Below are the 2 tables John has, and he wants to create table 3 out of the two tables:

We will go through the key steps to solve this problem:

• Step 1: Create a copy of table 2 with an empty column “Salary Per Annum”. Thus, have a separate table to perform Vlookup operation.

• Step 2: Entering Vlookup formula for first row in table 3, the first value of the formula is lookup_value. Lookup_value is the key which will be used to join the 2 tables. Lookup_value will be the value of the ID or the column which is present in both the tables and for which value has to be fetched. In the current situation Employee ID is the lookup_value, as we are searching for salary of each employee ID and will fetch the salary from table 2. Cell D19 is used for row 19, which will use Employee ID of row 19 to fetch data.

• Step 3: Table_Array is the next value to enter, this table array contains the additional field which we will fetch for the primary key. Enter cell J4:K12 to reference Table Array.

• Step 4: Fixing the Table_Array cells, Applying “$” sign with the table cells J4:k12 is important as we have to keep the table_array selection fixed.

• Step 5: Next value to enter is the col_index_number. This value refers to the numeric counter value in table 2 in which Salary is present, as Salary is present in Table 2 in 2nd column (Employee ID in first). Therefore, we will keep col_index_number as 2.

• Step 6: Last value to enter in the formula is [range_lookup] which can have 2 values, True or False. To do approximate match enter range_lookup as TRUE. To do exact match, enter value as FALSE. Accordingly, here we are using FALSE to do exact match.

• Step 7: Copying the formula present in J19 and pasting from cell J20 to J26. Hence, John is able to create what he was looking for, via Vlookup function.

Visit our video tutorial to further practice:

Looking to practice more with this example? Drop us a note, we will email you the Excel file:

To get top certifications in Microsoft Excel and build your resume visit here. Additionally, you can read books listed here to build strong knowledge around Microsoft Excel.