Method 1 – Using VLOOKUP Function to Transfer Data from One Worksheet of the Same Workbook to Another
Steps:
Using the VLOOKUP function, you need to look up values and, for this reason, copy or write down the first column’s data manually.
➤ Type the following formula in cell C4.
=VLOOKUP(B4,Record!B4:E12,2,FALSE)B4 is the lookup value, Record!B4:E12 is the data range of the Record sheet, 2 is the column number for the students’ names, and FALSE is for an exact match.
➤ Press ENTER and drag down the Fill Handle tool.
You will get all the names from the source dataset.
Use the following two formulas to transfer the data from the Subject and Marks columns to this sheet.
=VLOOKUP(B4,Record!B4:E12,3,FALSE)
=VLOOKUP(B4,Record!B4:E12,4,FALSE)
Method 2 – Using VLOOKUP Function with Named Range
Steps:
To work with the VLOOKUP function, copy or write down the first column’s data in the Student ID column.
➤ Type the following formula in cell C4.
=VLOOKUP(B4,marks,2,FALSE)B4 is the lookup value, marks is the named range of the Record sheet, 2 is the column number for the students’ names, and FALSE is for an exact match.
➤ Press ENTER and drag down the Fill Handle tool.
You will have the students’ names corresponding to the IDs in the Name column.
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 and MATCH Functions to Transfer Data from One Excel Worksheet to Another Automatically
Steps:
To use the VLOOKUP function, we need to look up values, so you must copy or write down the first column’s data 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)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.
You will have the student’s names in the Name column.
Use the following two formulas to move the data from the Subject and Marks columns 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
Steps:
Apply the following formula in cell C4.
=VLOOKUP(B4,'C:\Users\Mima\Downloads\[Source Data.xlsx]Data'!$B$4:$E$12,2,FALSE)B4 is the lookup value, $B$4:$E$12 is the data range of the Data worksheet in the Source Data.xlsx workbook, and you have declared the path of this file preceding the file name.
➤ Press ENTER and drag down the Fill Handle tool.
Get all of the names of the students in the Name column.
Use the following two formulas to have the data for the Subject column and the Marks column.
=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
Method 1 – Using INDEX-MATCH Formula to Transfer Data from One Excel Worksheet to Another
Steps:
Write down the data of the first column, Student ID, first.
➤ Type the following formula in cell C4.
=INDEX(Record!C$4:C$12,MATCH($B4,Record!$B$4:$B$12,0))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.
- 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.
You will have all the data from the source sheet on this sheet.
Method 2 – Using INDIRECT, ADDRESS, ROW, and COLUMN Functions to Transfer Data from One Worksheet to Another
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.
Move all of the data from our source datasheet to this sheet.
Download Workbook
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
Get FREE Advanced Excel Exercises with Solutions!
