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.

Download Workbook


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.

transfer data from one Excel worksheet to another automatically VLOOKUP

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

transfer data from one Excel worksheet to another automatically VLOOKUP

and using the VLOOKUP function we will transfer this data to the VLOOKUP sheet of that same book.

transfer data from one Excel worksheet to another automatically VLOOKUP

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.

VLOOKUP function

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

VLOOKUP function

➤ Press ENTER and drag down the Fill Handle tool.

VLOOKUP function

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

VLOOKUP function

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 function

=VLOOKUP(B4,Record!B4:E12,4,FALSE)

transfer data from one Excel worksheet to another automatically VLOOKUP

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.

transfer data from one Excel worksheet to another automatically VLOOKUP

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

transfer data from one Excel worksheet to another automatically VLOOKUP

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

VLOOKUP with Named Range

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

VLOOKUP with Named Range

➤ Press ENTER and drag down the Fill Handle tool.

VLOOKUP with Named Range

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

VLOOKUP with Named Range

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

=VLOOKUP(B4,marks,3,FALSE)

VLOOKUP with Named Range

=VLOOKUP(B4, marks,4, FALSE)

transfer data from one Excel worksheet to another automatically VLOOKUP


Similar Readings


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.

transfer data from one Excel worksheet to another automatically VLOOKUP

transfer data from one Excel worksheet to another automatically VLOOKUP

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.

VLOOKUP, MATCH Functions

➤ 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

VLOOKUP, MATCH Functions

➤ Press ENTER and drag down the Fill Handle tool.

VLOOKUP, MATCH Functions

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

VLOOKUP, MATCH Functions

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, MATCH Functions

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

transfer data from one Excel worksheet to another automatically VLOOKUP

Read More: How to Transfer Data from One Sheet to another in Excel Using Macros


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,

transfer data from one Excel worksheet to another automatically VLOOKUP

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

transfer data from one Excel worksheet to another automatically VLOOKUP

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.

transfer data from different workbook

➤ Press ENTER and drag down the Fill Handle tool.

transfer data from different workbook

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

transfer data from different workbook

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)

transfer data from different workbook

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

transfer data from different workbook


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.

transfer data from one Excel worksheet to another automatically VLOOKUP

transfer data from one Excel worksheet to another automatically VLOOKUP

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

transfer data from one Excel worksheet to another automatically VLOOKUP

➤ 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

INDEX-MATCH functions

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

INDEX-MATCH functions

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

INDEX-MATCH functions


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.

transfer data from one Excel worksheet to another automatically VLOOKUP

transfer data from one Excel worksheet to another automatically VLOOKUP

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(ROW(B4),COLUMN(B4)) becomes
    ADDRESS(4,2) → returns the reference of a cell with Row 4 and Column 4.
    Output → $B$4
  • INDIRECT(“Record!”&ADDRESS(ROW(B4),COLUMN(B4))) becomes
    INDIRECT(“Record!”&$B$4) INDIRECT(“Record!$B$4”)
    Output → 2010030

INDIRECT, ADDRESS Functions

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

INDIRECT, ADDRESS Functions

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

INDIRECT, ADDRESS Functions


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.

Practice


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

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

Leave a reply

ExcelDemy
Logo