How to use Vlookup formula between sheets in Excel

Excel VLOOKUP formula is one of the most widely used excel formulas. As the name suggests, Vlookup or vertical look up helps us to look up the required information using one data set reference in another data set. Combining with other formulas it can be used to analyse complex data sets.

Syntax : - VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

Below data sets belong to an organization. In Sheet 1, we have data containing  Employee Names and their employee IDs. In Sheet 2 we have the employee IDs and the Bonus detail for the year.

     Sheet 1 Data                                                                Sheet 2 Data

Now if we want to get the details of Bonus from the Sheet 2 to Sheet 1, we can use Vlookup formula.

Lets see how to populate the Bonus amount in column D using Vlookup formula. As shown in the image below , I have made column D heading (D1) as Bonus Amt. Now we will type in the required formula in D2 Cell as shown below.

= VLOOKUP(lookup_value, table_array  , col_index_num,[range_lookup])

= VLOOKUP(C2          ,'Sheet 2'!B:C ,      2       ,     FALSE    )

lookup_value = The value from current data set which we need as reference for the lookup . Here it is the Employee ID (C2 since formula is in row 2)

table_array = Range of data where we need to look up. It is range B:C in Sheet 2. You just have to select the range while typing the formula; it will take automatically pick as shown (ie. 'Sheet 2'!B:C). Always remember the first column should be where you need to search the lookup value. Here we are searching employee ID that is B column in sheet 2.

col_index_num = Index of column with required data in the table_array. Here, table_array is B:C, with column B whose index is 1 and Column C whose index is 2. Our required data (Bonus Amt.) is present in Column C and therefore col_index_num is 2. Always remember col_index_num is calculated based on the Table Array selected.

[range_lookup] = use always False or 0 to exactly match with reference. If you use True or 1, partial matches may also come in the result which may give incorrect results.

After typing in the formula, copy or fill down the formula till the last row to get the desired results. 

Completed result will be as given below

Above shows how to use basic Vlookup formula. You can combine it with other formulas like concatenate to create powerful formulas. In my coming posts, I will explain how to do that. Happy Looking up!