How to VLOOKUP between two Excel Files

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 with serial numbers

Source workbook

Worksheet Without serial numbers

Destination workbook

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

How to VLOOKUP between two excel files using formula

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

How to build your VLOOKUP formula from the menu

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.

Leave a Reply

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading