Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP

If you are looking for ways to transfer data from one Excel worksheet to another automatically with VLOOKUP, then this article will be helpful for you. Let’s get started with our main article to learn the ways of doing this job.

Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP: 4 Cases

Here, we have the following dataset containing the records of marks of some students of a college in the Record sheet. Using the following 6 methods we will transfer the data of this worksheet to another sheet.

We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.

Method-1: Using VLOOKUP Function to Transfer Data from One Worksheet of Same Workbook to Another

Here, we have the source dataset in the Record sheet of the Transfer Data to Another Sheet workbook, and using the VLOOKUP function we will transfer this data to the VLOOKUP sheet of that same book.

Steps:
➤ To use the VLOOKUP function, we need lookup values and for this reason, we have to copy or write down the data of the first column manually.

➤ Type the following formula in cell C4.

Here, B4 is the lookup value, Record!B4:E12 is the data range of the Record sheet, 2 is the column number for the names of the students, and FALSE is for an exact match.

➤ Press ENTER and drag down the Fill Handle tool.

In this way, you will get all of the names from the source dataset.

Similarly, use the following two formulas for transferring the data of the Subject and Marks column to this sheet.

Method-2: Using VLOOKUP Function with Named Range

To reference the source dataset easily, named ranges are helpful to use with the VLOOKUP function.

So, here we have named the range of the Record sheet to transfer these values to the Named Range sheet easily.

Steps:
➤ To work with the VLOOKUP function, copy or write down the data of the first column in the Student Id column.

➤ Type the following formula in cell C4.

Here, B4 is the lookup value, marks is the named range of the Record sheet, 2 is the column number for the names of the students, and FALSE is for an exact match.

➤ Press ENTER and drag down the Fill Handle tool.

Then, you will have the names of the students corresponding to the Ids in the Name column.

Likewise, apply the following formulas to extract the subjects and marks of the students.

Method-3: Using VLOOKUP, MATCH Functions to Transfer Data from One Excel Worksheet to Another Automatically

In this section, we are going to use the combination of the VLOOKUP and MATCH functions to transfer the data from the Record worksheet to the VLOOKUP, MATCH sheet.

Steps:
➤ To use the VLOOKUP function, we need lookup values and that’s why we have to copy or write down the data of the first column manually.

➤ Type the following formula in cell C4.

Here, B4 is the lookup value, and Record!B3:E12 is the data range of the Record sheet.

➤ Press ENTER and drag down the Fill Handle tool.

After that, you will have the names of the students in the Name column.

In the same way, use the following two formulas for moving the data of the Subject and Marks column to this sheet.

Method-4: Using VLOOKUP Function to Transfer Data from One Worksheet to Another Workbook Automatically

In this section, we will transfer data from a worksheet to another sheet of a different workbook. Our source dataset is in the Data worksheet of the Source Data.xlsx workbook,

and we will transfer the data to the different workbook sheets of the Transfer Data to Another Sheet.xlsx workbook.

Steps:
➤ Apply the following formula in cell C4.

Here, B4 is the lookup value, $B$4:$E$12 is the data range of the Data worksheet in the Source Data.xlsx workbook, and preceding the file name we have declared the path of this file.

➤ Press ENTER and drag down the Fill Handle tool.

Finally, we will get all of the names of the students in the Name column.

To have the data of the Subject column and the Marks column use the following two formulas.

Useful Alternatives to Transfer Data with VLOOKUP

So far we’ve used the VLOOKUP function to do the task, Excel provides us with other functions that work as VLOOKUP. Let’s see the alternatives.

Method-1: Using INDEX-MATCH Formula to Transfer Data from One Excel Worksheet to Another

Here, we will be using the combination of the INDEX and MATCH functions to transfer the data of the Record worksheet to the INDEX-MATCH sheet.

Steps:
➤ Write down the data of the first column Student Id first.

➤ Type the following formula in cell C4.

Here, B4 is the lookup value, Record!$B$4:$B$12 is the lookup range of the Record sheet and C$4:C$12 is the range containing output values.

➤ Press ENTER.
➤ Drag the Fill Handle tool to the right and down.

Eventually, you will have all of the data from the source sheet on this sheet.

Method-2: Using INDIRECT, ADDRESS, ROW, COLUMN Functions to Transfer Data from One Worksheet to Another

In this section, we will use the INDIRECT, ADDRESS, ROW, and COLUMN functions for transferring the records of the Record sheet to the INDIRECT-ADDRESS sheet.

Steps:
➤ Apply the following formula in cell B4.

➤ Press ENTER.
➤ Drag the Fill Handle tool to the right and down.

Ultimately, we will move all of the data from our source datasheet to this sheet.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to cover the ways to transfer data from one Excel worksheet to another automatically with VLOOKUP. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• 10 Best Practices with VLOOKUP in Excel
• 7 Practical Examples of VLOOKUP Function in Excel
• VLOOKUP Example Between Two Sheets in Excel
• How to Use Dynamic VLOOKUP in Excel
• How to Make VLOOKUP Case Sensitive in Excel
• VLOOKUP from Another Sheet in Excel
• How to Use VLOOKUP Formula in Excel with Multiple Sheets
• How to Remove Vlookup Formula in Excel
• How to Apply VLOOKUP to Return Blank Instead of 0 or NA
• How to Hide VLOOKUP Source Data in Excel
• How to Copy VLOOKUP Formula in Excel

<< Go Back to VLOOKUP Between Worksheets | Excel VLOOKUP Function | Excel Functions | Learn Excel