About retrieving data

lfk-amal 

To retrieve data from another workbook in Excel and add a new list in the 2nd workbook containing cities from the 1st workbook, you can use Excel's "VLOOKUP" function or "INDEX" and "MATCH" functions. Here is a step-by-step guide using the VLOOKUP method:

Assuming you have two workbooks:

1. Workbook 1 contains emails and cities.
• Email addresses are in column A.
• Cities are in column B.
2. Workbook 2 contains emails and hackerspaces.
• Email addresses are in column A.
• Hackerspaces are in column B.

You want to add a new list in Workbook 2 that includes cities based on matching email addresses from Workbook 1.

Follow these steps:

In Workbook 2:

1. Open Workbook 2.
2. In the cell next to the first email address in Workbook 2 (e.g., cell C2), enter the following formula:

=VLOOKUP(A2, '[Workbook1.xlsx]Sheet1'!$A$1:$B$100, 2, FALSE)

• Replace "Workbook1.xlsx" with the actual name of Workbook 1.
• Replace "Sheet1" with the name of the sheet in Workbook 1 where your data is located.
• Adjust the range $A$1:$B$100 to cover the entire range of email addresses and cities in Workbook 1.
1. Drag the fill handle (a small square at the bottom right corner of the cell) down to fill the formula for all email addresses in Workbook 2.
2. The cities corresponding to the email addresses in Workbook 1 will be retrieved and displayed in Workbook 2.
3. You can now copy and paste this new list of cities wherever you need it in Workbook 2.

If Workbook 1 is closed, you should specify the full file path in the VLOOKUP formula. For example:

=VLOOKUP(A2, '[C:\Path\To\Workbook1.xlsx]Sheet1'!$A$1:$B$100, 2, FALSE)

• Replace C:\Path\To\Workbook1.xlsx with the actual file path to Workbook 1.

The VLOOKUP function searches for a value (the email address in this case) in the first column of a specified range (in Workbook 1) and returns a value (the city) from a specified column (the 2nd column in this case).

Remember to adjust the cell references and ranges as needed to match the actual locations of your data in both workbooks. The text, steps and formulas was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.