How to copy data from one Excel sheet to another using VLOOKUP 

The process of using VLOOKUP in Excel to copy data from one sheet to another involves automatically transferring information between worksheets by matching a particular value. VLOOKUP, short for “Vertical Lookup,” is an Excel formula that enables you to search for a specific value within a range or table and obtain a related piece of information from it.

In this tutorial, we will learn how to copy data from one Excel sheet to another using VLOOKUP. The VLOOKUP function is a pre-built formula within Microsoft Excel that enables you to find a value in the first column of a table and retrieve a related value from a specified column in the same row.

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

• The lookup_value is the value that you want to search for in the left-most column of the table_array, and it can be either a cell reference, a text string, or a numeric value.

• The table_array is the range of cells where the function searches for the lookup_value, and it must contain the leftmost column that you want to search and the column from which you want to retrieve the result.

• The col_index_num is the number of the column that contains the result you want to retrieve, starting from the left-most column in the table_array. 

Currently, we have a data set representing the sales data of a company according to the date in the Original Sheet. We want to copy it to another sheet i.e. the Final Sheet utilizing the VLOOKUP function.

Method 1: Copy Data from One Excel Sheet to Another by Utilizing the VLOOKUP Function in Each Column

Step 1 – Copy and Paste the Column Containing the Lookup Values 

• Copy and paste the column containing the original lookup values to the final sheet.

Step 2 – Select the First Cell of the First Column to be Extracted

• Select the first cell of the first column to be extracted.

Step 3 – Utilize the VLOOKUP Function

• Utilize the VLOOKUP function in the cell.
• The syntax to extract the data for the selected cell, in this case, will be: 

             VLOOKUP($A2,OriginalSheet!$A$1:$E$9,2,FALSE)

• A2 is the first lookup value.
• OriginalSheet!A1:E9 is the table array i.e. the original data.
• 2 ( the third parameter) is the column index i.e. the column of the table array from which the data is to be extracted.
• The fourth parameter i.e. FALSE is the type of the match, it is an optional parameter.

Step 4 – Press the “Enter” Key

• Press the Enter key.
• The data for the selected cell i.e. the first cell of the first column will be posted.

Step 5 – Now Utilize the VLOOKUP Function in the First Cell of Each Column

• Following the same steps, utilize the VLOOKUP function in the first cell of each column.

Step 6 – Choose the First Cell of Each Column

• Choose the first cell of each column in which the VLOOKUP function is used.

Step 7 – Utilize Autofill to Copy and Paste Each Column in the Final Sheet

• Utilize Autofill collectively on all the selected cells to copy and paste the data of each column in the final sheet.

Method 2: Utilizing the Autofill with VLOOKUP to Copy Data From Another Sheet 

Step 1 – Copy and Paste the Column Containing the Lookup Values 

• Copy and paste the column containing the original lookup values to the final sheet.

Step 2 – Select the First Cell of the First Column to be Extracted

• Select the first cell of the first column to be extracted.

Step 3 – Utilize the VLOOKUP Function

• Utilize the VLOOKUP function in the cell.
• The syntax to extract the data for the selected cell, in this case, will be: 

             VLOOKUP(A2,OriginalSheet!A1:E9,2,FALSE)

• Unlike the previous method, the lookup cell reference and the table array will not be locked in this method.

Step 4 – Press the “Enter” Key

• Press the Enter key.
• The data for the selected cell i.e. the first cell of the first column will be posted.

Step 5 – Utilize Autofill Function Across the Row 

• Utilize Autofill to extract the complete row from the original data.
• Utilize Autofill down the column, data for each column will be copied automatically.