How to do a vlookup in excel between two workbooks

Answer

A VLOOKUP in Excel retrieves data from a table based on a matching value. When working between two workbooks, VLOOKUP can pull information from a workbook different from the one where the formula is entered.

Steps to Perform VLOOKUP Between Two Workbooks

1. Open Both Workbooks
   Open the workbook containing the lookup value (source workbook) and the workbook containing the table you want to retrieve data from (target workbook).

2. Select the Cell for the VLOOKUP Formula
   Click on the cell where you want the result to appear in the source workbook.

3. Enter the VLOOKUP Formula
   The general syntax for VLOOKUP is:

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

   • lookup_value: The value you want to search for in the target workbook.

   • table_array: The range in the target workbook where the lookup will occur.

   • col_index_num: The column number in the table array from which to retrieve the data.

   • range_lookup: Enter FALSE for an exact match or TRUE for an approximate match.

4. Reference the Other Workbook
   While entering the table_array, switch to the target workbook and select the range. Excel automatically creates an external reference that includes the workbook name in square brackets, the worksheet name, and the range. For example:

   =[TargetWorkbook.xlsx]Sheet1!$A$2:$D$100

5. Complete the Formula
   Finish the formula by specifying the column number and match type. Example for an exact match:

   =VLOOKUP(A2, [TargetWorkbook.xlsx]Sheet1!$A$2:$D$100, 3, FALSE)

6. Press Enter
   The cell will display the value from the target workbook corresponding to the lookup value.

7. Ensure Workbooks Stay Accessible
   The source workbook will retain the external link. Closing the target workbook will not break the formula, but any updates in the target workbook require reopening it to refresh the data.

Tips for Using VLOOKUP Between Workbooks

1. Use absolute references ($A$2:$D$100) to prevent changes in the range when copying the formula.

2. Avoid spaces or special characters in workbook or sheet names. If present, Excel will automatically wrap the name in single quotes:

   ='[Target Workbook.xlsx]Sheet 1'!$A$2:$D$100

3. Always specify FALSE for range_lookup to prevent mismatched data when exact matches are needed.

4. Consider using XLOOKUP (Excel 365 or 2021) for more flexibility, as it allows horizontal and vertical lookups and avoids errors from column index changes.

Example

Suppose Workbook1.xlsx has a list of employee IDs in column A, and Workbook2.xlsx contains employee details with IDs in column A and salaries in column C. To retrieve salaries from Workbook2.xlsx into Workbook1.xlsx:

=VLOOKUP(A2, [Workbook2.xlsx]Employees!$A$2:$C$100, 3, FALSE)

This formula searches for the employee ID in A2 of Workbook1.xlsx, looks it up in column A of Workbook2.xlsx, and returns the corresponding salary from column C.

This approach ensures dynamic updates and accurate cross-workbook data retrieval.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report