How to Use VLOOKUP From Another Workbook in Excel

VLOOKUP (Vertical Lookup) is a powerful function in Microsoft Excel that allows you to find and retrieve data from one table or range based on a specified value. It can be especially useful when you need to look up data from another workbook.

Here's a step-by-step guide on how to use VLOOKUP from another workbook in Excel:

1. Open both the workbooks - the workbook containing the data you want to look up (source workbook), and the workbook where you want to apply the VLOOKUP function (destination workbook).
2. In the destination workbook, click on the cell where you want to display the result of the VLOOKUP function.
3. Type the VLOOKUP formula: =VLOOKUP(lookup_value, [source workbook]worksheet!range, column_index, [range_lookup])
   • lookup_value: The value you want to search for in the source workbook.
   • source workbook: The name of the source workbook containing the data you want to look up.
   • worksheet: The name of the worksheet in the source workbook containing the data you want to look up.
   • range: The range of cells in the source workbook that you want to search in.
   • column_index: The column number in the range containing the value you want to return.
   • range_lookup: This is an optional argument. Enter FALSE for an exact match and TRUE for an approximate match.
4. Press Enter to complete the formula.

Example

Let's say you have two workbooks: SalesData.xlsx and Summary.xlsx. You want to use VLOOKUP in Summary.xlsx to find the sales figures for a specific product from the data in SalesData.xlsx.

1. Open both workbooks: SalesData.xlsx and Summary.xlsx.
2. In Summary.xlsx, click on the cell where you want to display the sales figures for a specific product.
3. Type the VLOOKUP formula:

   =VLOOKUP(A2, [SalesData.xlsx]Sheet1!$A$1:$B$10, 2, FALSE)

   • A2: The cell containing the product name you want to search for in SalesData.xlsx.
   • [SalesData.xlsx]Sheet1!$A$1:$B$10: The range of cells in the source workbook that you want to search in. This includes the product names in column A and the sales figures in column B.
   • 2: The column number in the range containing the sales figures you want to return.
   • FALSE: We are looking for an exact match.
4. Press Enter to complete the formula.

Now, the VLOOKUP function will search for the product in SalesData.xlsx and display the corresponding sales figures in Summary.xlsx. If you change the product name in cell A2 of Summary.xlsx, the VLOOKUP will update the result accordingly.