Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# 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 know the ways of doing this job.

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

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 Microsoft Excel 365 version here, you can use any other versions 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.

`=VLOOKUP(B4,Record!B4:E12,2,FALSE)`

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.

`=VLOOKUP(B4,Record!B4:E12,3,FALSE)` `=VLOOKUP(B4,Record!B4:E12,4,FALSE)` Read More: Transfer Data from One Excel Worksheet to Another Automatically

### 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.

`=VLOOKUP(B4,marks,2,FALSE)`

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.

`=VLOOKUP(B4,marks,3,FALSE)` `=VLOOKUP(B4, marks,4, FALSE)` ### 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.

`=VLOOKUP(B4,Record!\$B\$3:\$E\$12,MATCH(Record!C\$3,Record!B\$3:E\$3),FALSE)`

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

• MATCH(Record!C\$3, Record!B\$3:E\$3) becomes
MATCH(“Name”, Record!B\$3:E\$3) → MATCH will give the column index number of the header “Name” in the range of headers B\$3:E\$3 in the Record sheet.
Output → 2
• VLOOKUP(B4,Record!\$B\$3:\$E\$12,MATCH(Record!C\$3,Record!B\$3:E\$3),FALSE) becomes
VLOOKUP(2010030, Record!\$B\$3:\$E\$12,2, FALSE) → returns the name of the student for the ID 2010030
Output → Joseph ➤ 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.

`=VLOOKUP(B4,Record!\$B\$3:\$E\$12,MATCH(Record!D\$3,Record!B\$3:E\$3),FALSE)` `=VLOOKUP(B4,Record!\$B\$3:\$E\$12,MATCH(Record!E\$3,Record!B\$3:E\$3,0),FALSE)` ### 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 sheet of the Transfer Data to Another Sheet.xlsx workbook. Steps:
➤ Apply the following formula in cell C4.

`=VLOOKUP(B4,'C:\Users\Mima\Downloads\[Source Data.xlsx]Data'!\$B\$4:\$E\$12,2,FALSE)`

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.

`=VLOOKUP(B4,'C:\Users\Mima\Downloads\[Source Data.xlsx]Data'!\$B\$4:\$E\$12,3,FALSE)` `=VLOOKUP(B4,'C:\Users\Mima\Downloads\[Source Data.xlsx]Data'!\$B\$4:\$E\$12,4,FALSE)` ## Useful Alternatives to Transfer Data with VLOOKUP

So far we’ve used the VLOOKUP function to do the task, Excel provides us other functions that too 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 firstly. ➤ Type the following formula in cell C4.

`=INDEX(Record!C\$4:C\$12,MATCH(\$B4,Record!\$B\$4:\$B\$12,0))`

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

• MATCH(\$B4,Record!\$B\$4:\$B\$12,0) becomes
MATCH(2010030, Record!\$B\$4:\$B\$12,0) → MATCH will give the row index number of the value 2010030 in the range \$B\$4:\$B\$12 of the Record sheet.
Output → 1
• INDEX(Record!C\$4:C\$12,MATCH(\$B4,Record!\$B\$4:\$B\$12,0)) becomes
INDEX(Record!C\$4:C\$12,1,0)) → returns the name of the student for the ID 2010030
Output → Joseph ➤ 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.

`=INDIRECT("Record!"&ADDRESS(ROW(B4),COLUMN(B4)))`
• ROW(B4) → returns the row number of the cell B4
Output → 4
• COLUMN(B4) → returns the column number of the cell B4
Output → 2
ADDRESS(4,2) → returns the reference of a cell with Row 4 and Column 4.
Output → \$B\$4
INDIRECT(“Record!”&\$B\$4) INDIRECT(“Record!\$B\$4”)
Output → 2010030 ➤ 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. ## 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 #### Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts 