Retrieve Data from Another Workbook
This article explains how to retrieve information from multiple Excel workbooks. By following these methods, you can easily reference and obtain data scattered across workbooks for analysis or report preparation.
Using VLOOKUP to Retrieve Data from Another Workbook
VLOOKUP is a simple yet powerful function to search for and retrieve specific data from another workbook.
Source Workbook: SalesWorkbook.xlsx (Sheet1)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product Name | Price | Category |
| 2 | 101 | Product A | 2000 | Food |
| 3 | 102 | Product B | 3500 | Beverage |
| 4 | 103 | Product C | 1800 | Food |
Calling Workbook
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 102 |
Steps
- Enter the Product ID in cell A1 (e.g., 102).
- In cell B1, input the following VLOOKUP formula:
=VLOOKUP(A1, '[SalesWorkbook.xlsx]Sheet1!$A$2:$D$4', 2, FALSE)
How the Formula Works
The VLOOKUP function searches for the value in cell A1 (Product ID) and retrieves the corresponding Product Name from the specified range in the source workbook.
Result
When cell A1 contains 102, cell B1 displays Product B.
Using XLOOKUP to Retrieve Data from Another Workbook
XLOOKUP offers greater flexibility for retrieving values between workbooks.
Steps
- Enter the Product ID in cell A1 (e.g., 103).
- In cell B1, input the following XLOOKUP formula:
=XLOOKUP(A1, '[SalesWorkbook.xlsx]Sheet1!$A$2:$A$4', '[SalesWorkbook.xlsx]Sheet1!$B$2:$B$4')
How the Formula Works
The XLOOKUP function searches for the Product ID in column A of the source workbook and returns the corresponding Product Name from column B.
Result
When cell A1 contains 103, cell B1 displays Product C.
Using INDEX and MATCH to Retrieve Data from Another Workbook
By combining INDEX and MATCH, you can also retrieve data from a different workbook.
Steps
- Enter the Product ID in cell A1 (e.g., 101).
- In cell B1, input the following formula:
=INDEX('[SalesWorkbook.xlsx]Sheet1!$B$2:$B$4', MATCH(A1, '[SalesWorkbook.xlsx]Sheet1!$A$2:$A$4', 0))
How the Formula Works
The INDEX function retrieves data from a specified range (column B, Product Names), while the MATCH function determines the position of the Product ID in column A.
Result
When cell A1 contains 101, cell B1 displays Product A.
Conclusion
Retrieving data from multiple Excel workbooks is made easy using functions like VLOOKUP, XLOOKUP, or a combination of INDEX and MATCH. Choose the function that best suits your data structure and requirements.