How do i pull data from one sheet to another in excel using 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.

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)

Similar Readings

• VBA Code to Convert Text File to Excel (7 Methods)
• How to Import Data into Excel from Another Excel File (2 Ways)
• How to Import Text File with Multiple Delimiters into Excel (3 Methods)
• Excel VBA: Pull Data Automatically from a Website (2 Methods)
• How to Convert Excel to Text File with Pipe Delimiter (2 Ways)

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)

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,

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(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

➤ 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

• Import Data from Excel into Word Automatically Using VBA (2 Ways)
• How to Extract Data from a List Using Excel Formula (5 Methods)
• How to Extract Year from Date in Excel (3 Ways)
• Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
• How to Pull Data from Multiple Worksheets in Excel VBA
• How to Extract Specific Data from a Cell in Excel (3 Examples)