VLOOKUP Data from Another Workbook as per List of Sheets Name

One of the most useful functions in Microsoft Excel is VLOOKUP. It allows you to search for a specific value in a column and retrieve data from another column in the same row. However, what if you need to perform a VLOOKUP across multiple sheets in another workbook? In this article, we will guide you through the process of using VLOOKUP to fetch data from another workbook based on a list of sheet names.

Before we begin, make sure that both workbooks are open in Excel. Let's assume that the workbook containing the data you want to retrieve is called "DataWorkbook.xlsx" and the workbook where you want to perform the VLOOKUP is called "MainWorkbook.xlsx".

The first step is to create a list of sheet names in the Main Workbook. To do this, open the Main Workbook and navigate to the sheet where you want to display the results. In cell A1, enter the following formula:

This formula will retrieve the names of the sheets in the Data Workbook. Replace "Sheet1:Sheet3" with the actual range of sheets you want to include in the list. Once you enter the formula, press Enter to see the list of sheet names.

Next, we need to set up the VLOOKUP formula to fetch data from the Data Workbook based on the sheet names in the Main Workbook. In cell B1 of the Main Workbook, enter the following formula:

Let's break down this formula:

• A1 is the cell that contains the sheet name in the Main Workbook.
• INDIRECT("'[DataWorkbook.xlsx]'!"&A1&"!A:B") is used to create a dynamic reference to the range of data in the Data Workbook. The INDIRECT function converts the text string into a valid reference.
• 2 specifies that we want to retrieve the value from the second column of the range (column B).
• FALSE indicates that we want an exact match when searching for the sheet name.

Once you enter the formula, press Enter to fetch the data from the Data Workbook based on the sheet name in cell A1.

To apply the formula to the remaining cells in column B, simply drag the fill handle (the small square at the bottom right corner of the cell) down to copy the formula to the desired range.

Now you should see the data retrieved from the Data Workbook in the Main Workbook, based on the sheet names listed in column A.

Remember to save both workbooks after entering the formulas to ensure that the data remains updated.

That's it! You have successfully used VLOOKUP to fetch data from another workbook as per a list of sheet names. This technique can be extremely useful when you have multiple sheets in a workbook and need to consolidate data from different sources.

Summary

In this article, we learned how to use VLOOKUP to fetch data from another workbook based on a list of sheet names. By creating a dynamic reference using the INDIRECT function, we were able to retrieve data from specific sheets in the Data Workbook. This technique can be a powerful tool for data analysis and consolidation in Excel.

Explore our wide range of tech support articles to enhance your knowledge and troubleshoot various issues with ease.