VLOOKUP is one of the most used Excel functions. It helps to look up data in the same worksheet, different worksheet, or different workbook book easily.
Most people are used to looking up values from the same Workbook but not separate Workbook. For this reason, I will guide you on how to VLOOKUP between two Excel files.
How to VLOOKUP between two workbooks
Let’s assume you have two workbooks containing the same data, but one isn’t filled completely. You can use the Excel VLOOKUP function to pull the data from the separate Workbook. If you wish to follow along, you can download these two worksheets.
Worksheet Without serial numbers
How to VLOOKUP between two Excel Files using Formula
It is important to save both files under the same folder to have a smooth lookup of data.
1. Open the spreadsheet that you want to fill in the data
Open the spreadsheet that is missing data. This is where you will enter the Formula that pulls data from the other Workbook.
2. This is the syntax that you are going to use
‘[workbook]sheet’!range
Workbook refers to the name, including the file extension of the Workbook containing data.
The sheet is the sheet name containing the data you want to pull
Lastly, range refers to the cells containing the data you are looking up.
3. Type the following Formula in the Workbook with missing data
=VLOOKUP(A2,'[workbook with serial numbers.xlsx]Sheet1′!$A$2:$B$11,2,FALSE) and click enter.
The function will look up the serial numbers from the other Workbook and populate it. Note that it uses Absolute referencing so that you can apply the Formula to other cells without changing the range.
4. Double-click the fill handle to fill in the data
All the missing serial numbers will be automatically looked up from the other Excel file and inserted on the current sheet automatically.
How to build the VLOOKUP formula
It is easy to build your own VLOOKUP formula to lookup for data between two Excel files.
1. Click on formulas>Lookup & References, then select VLOOKUP from the drop-down list
2. Under LOOKUP value, select the first cell
3. Under table array, select the table range on the other worksheet
4. Column index number, type the number of the column containing the data you are looking for. For instance, in this example, the column index is 2.
5. Under Range, select the Logic False
How to VLOOKUP between two sheets using VBA
You can also use the VBA code to lookup up data from another sheet. Follow these simple steps.
1. Open the VBA editor by using the shortcut ALT+F11 or fn+opt+f11
2. Click on Insert, then select Module
3. Now copy and paste the code below into the editor
Sub VLOOKUPBetweenSheets()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim lookupRange As Range
Dim lookupValue As Range
Dim resultColumn As Integer
' Set the source and destination sheets
Set sourceSheet = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the actual name of your source sheet
Set destinationSheet = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to the actual name of your destination sheet
' Set the lookup range in the source sheet
Set lookupRange = sourceSheet.Range("A1:B10") ' Change the range to match your data
' Set the lookup value in the destination sheet
Set lookupValue = destinationSheet.Range("A1") ' Change the cell reference to match your lookup value
' Set the column number for the result
resultColumn = 2 ' Change to the appropriate column number
' Perform the VLOOKUP
lookupValue.Offset(0, resultColumn - 1).Value = Application.VLookup(lookupValue.Value, lookupRange, resultColumn, False)
End Sub
4. Replace destinationworkbook.xlsx with your actual destination workbook. Also, replace sourceworkbook.xlsx with the actual name of the source workbook
5. Lastly, set the lookup value and the correct data range and click on the green run button.
The VBA code will VLOOKUP between the two workbooks.
Final Thoughts
In this article, we have learned how to look up values between two excel Files. You can build your Formula using the context menu or use the VBA code.