VLookup using Excel sheet to get data from another workbook

If you are new to Excel and want to learn how to use VLookup to retrieve data from another workbook, you've come to the right place! VLookup is a powerful function in Excel that allows you to search for a value in a table and return a corresponding value from another column. In this article, we will guide you through the process of using VLookup to get data from another workbook.

Step 1: Open both workbooks

The first step is to open both the workbook from which you want to retrieve the data (let's call it Workbook A) and the workbook where you want to place the data (Workbook B). Make sure both workbooks are saved in a location that is easily accessible.

Step 2: Set up the VLookup formula

In Workbook B, select the cell where you want the retrieved data to appear. This is usually the cell in the same row as the data you want to match. Now, enter the following formula:

Let's break down this formula:

• lookup_value: This is the value you want to search for in Workbook A. It can be a specific value, a cell reference, or a formula.
• '[Workbook A.xlsx]Sheet1'!table_array: This is the range of cells in Workbook A where you want to search for the lookup value. Make sure to include the workbook name, sheet name, and the range of cells.
• col_index_num: This is the column number in the table_array from which you want to retrieve the data. Count the columns starting from the leftmost column in the table_array.
• [range_lookup]: This is an optional argument. If set to TRUE or omitted, VLookup will find an approximate match. If set to FALSE, VLookup will only find an exact match.

Step 3: Replace the arguments with appropriate values

Now, you need to replace the arguments in the formula with the appropriate values based on your data. For example, if you want to retrieve the price of a product based on its ID, your formula may look like this:

In this example, A2 is the cell containing the ID you want to search for, '[Workbook A.xlsx]Sheet1'!$A$2:$C$100 is the range of cells in Workbook A where the ID and price are located, 3 represents the third column in the range (which is the price column), and FALSE ensures an exact match.

Step 4: Press Enter and retrieve the data

After entering the formula, press Enter. If the lookup value is found in Workbook A, the corresponding value from the specified column will be displayed in the cell where you entered the formula in Workbook B. If the lookup value is not found, Excel will return an #N/A error.

That's it! You have successfully used VLookup to retrieve data from another workbook. Remember to save your workbooks to preserve the formulas and data.

Conclusion

VLookup is a valuable tool in Excel that allows you to quickly retrieve data from another workbook. By following the steps outlined in this article, even entry-level users can harness the power of VLookup to enhance their data analysis and reporting tasks.

References