5 Ways To Vlookup Between Two Excel Sheets

VLOOKUP is one of the most popular and useful functions in Excel, allowing users to search for a value in a table and return a corresponding value from another column. However, the classic VLOOKUP function has its limitations, especially when it comes to searching between two different Excel sheets. In this article, we will explore five ways to perform a VLOOKUP between two Excel sheets, including using the VLOOKUP function, INDEX-MATCH, Power Query, and more.

Understanding the Classic VLOOKUP Function

Before we dive into the five ways to perform a VLOOKUP between two Excel sheets, let's quickly review how the classic VLOOKUP function works. The VLOOKUP function takes four arguments: the value to search for, the range of cells to search in, the column number to return, and an optional range lookup value. The syntax is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The classic VLOOKUP function has several limitations, including:

• It can only search for a value in the first column of the table array.
• It can only return a value from a column to the right of the search column.
• It is not flexible when it comes to searching between two different Excel sheets.

Method 1: Using the VLOOKUP Function with Multiple Sheets

One way to perform a VLOOKUP between two Excel sheets is to use the VLOOKUP function with multiple sheets. This method involves creating a named range that references the data in the second sheet, and then using the VLOOKUP function to search for a value in that named range.

Here's an example:

Suppose we have two sheets, "Sheet1" and "Sheet2", with the following data:

Sheet1:

Employee ID	Name
101	John Smith
102	Jane Doe
103	Bob Johnson

Sheet2:

Employee ID	Department
101	Sales
102	Marketing
103	IT

To perform a VLOOKUP between the two sheets, we can create a named range that references the data in Sheet2:

1. Go to Formulas > Define Name.
2. In the New Name dialog box, enter a name for the range, such as "Department".
3. In the Refers to box, enter the following formula: =Sheet2!$A$1:$B$3
4. Click OK.

Now we can use the VLOOKUP function to search for a value in the named range:

=VLOOKUP(A2, Department, 2, FALSE)

This formula searches for the value in cell A2 (the employee ID) in the named range "Department", and returns the corresponding department from the second column.

Method 2: Using the INDEX-MATCH Function

Another way to perform a VLOOKUP between two Excel sheets is to use the INDEX-MATCH function. This method is more flexible than the classic VLOOKUP function and can search for a value in any column.

Here's an example:

Suppose we have the same data as before:

Sheet1:

Employee ID	Name
101	John Smith
102	Jane Doe
103	Bob Johnson

Sheet2:

Employee ID	Department
101	Sales
102	Marketing
103	IT

To perform a VLOOKUP between the two sheets using the INDEX-MATCH function, we can use the following formula:

=INDEX(Sheet2!$B$1:$B$3, MATCH(A2, Sheet2!$A$1:$A$3, 0))

This formula searches for the value in cell A2 (the employee ID) in the range Sheet2!$A$1:$A$3, and returns the corresponding department from the range Sheet2!$B$1:$B$3.

Method 3: Using Power Query

Power Query is a powerful tool in Excel that allows us to import and transform data from various sources, including other Excel sheets. We can use Power Query to perform a VLOOKUP between two Excel sheets.

Here's an example:

Suppose we have the same data as before:

Sheet1:

Employee ID	Name
101	John Smith
102	Jane Doe
103	Bob Johnson

Sheet2:

Employee ID	Department
101	Sales
102	Marketing
103	IT

To perform a VLOOKUP between the two sheets using Power Query, we can follow these steps:

1. Go to Data > New Query.
2. In the Query Editor, click on "From Other Sources" and select "From Microsoft Query".
3. In the Microsoft Query dialog box, select the range Sheet2!$A$1:$B$3 and click OK.
4. In the Query Editor, click on "Add Column" and select "Custom Column".
5. In the Custom Column dialog box, enter the following formula: =Table.Join(Sheet1, "Employee ID", Table, "Employee ID", {"Department"})
6. Click OK.

Now we can load the query into a new sheet and perform a VLOOKUP between the two sheets.

Method 4: Using VBA Macros

We can also use VBA macros to perform a VLOOKUP between two Excel sheets. This method requires some programming knowledge, but it can be very powerful.

Here's an example:

Suppose we have the same data as before:

Sheet1:

Employee ID	Name
101	John Smith
102	Jane Doe
103	Bob Johnson

Sheet2:

Employee ID	Department
101	Sales
102	Marketing
103	IT

To perform a VLOOKUP between the two sheets using VBA macros, we can create a new module in the Visual Basic Editor and enter the following code:

Sub VLOOKUPBetweenSheets()
  Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  Dim employeeID As String
  Dim department As String
  
  Set ws1 = ThisWorkbook.Worksheets("Sheet1")
  Set ws2 = ThisWorkbook.Worksheets("Sheet2")
  
  employeeID = ws1.Range("A2").Value
  
  department = Application.VLookup(employeeID, ws2.Range("A1:B3"), 2, False)
  
  ws1.Range("C2").Value = department
End Sub

This code searches for the value in cell A2 (the employee ID) in the range Sheet2!$A$1:$B$3, and returns the corresponding department to cell C2.

Method 5: Using Excel Formulas with Multiple Criteria

Finally, we can use Excel formulas with multiple criteria to perform a VLOOKUP between two Excel sheets. This method involves using the INDEX-MATCH function with multiple criteria.

Here's an example:

Suppose we have the same data as before:

Sheet1:

Employee ID	Name
101	John Smith
102	Jane Doe
103	Bob Johnson

Sheet2:

Employee ID	Department	Location
101	Sales	New York
102	Marketing	Chicago
103	IT	Los Angeles

To perform a VLOOKUP between the two sheets using Excel formulas with multiple criteria, we can use the following formula:

=INDEX(Sheet2!$C$1:$C$3, MATCH(1, (Sheet2!$A$1:$A$3=A2)*(Sheet2!$B$1:$B$3="Sales"), 0))

This formula searches for the value in cell A2 (the employee ID) and "Sales" in the range Sheet2!$A$1:$C$3, and returns the corresponding location from the range Sheet2!$C$1:$C$3.

Gallery of VLOOKUP Between Two Excel Sheets

VLOOKUP Between Two Excel Sheets Image Gallery

Conclusion

In this article, we explored five ways to perform a VLOOKUP between two Excel sheets. We covered the classic VLOOKUP function, INDEX-MATCH, Power Query, VBA macros, and Excel formulas with multiple criteria. Each method has its own strengths and weaknesses, and the best approach will depend on the specific requirements of your project.