help with creating a formula to extract data

[WhtMtnSteve]

I'm a newbie and don't even know where to start with this. I have two workbooks, W1 & W2, each having multiple worksheets but I'm only interested in Sheet1 in W1 and Sheet2 in W2. Both S1 and S2 have columns with "Phone Numbers". S1 and S2 also have columns A, B and C (ID#, Last Name and First Name). The steps that I have been doing are:

1- find and copy a phone number from W1S1
2- find that phone number in W2S2
3- copy info from three columns in the row with the phone number from W2S2
4- paste the info from the three columns in W2S2 into the three columns in W1S1 in the same row containing the phone number that was copied from step 1.

I'm wondering if there might be a formula that could accomplish this.

Thanks in advance!

[#2]

@WhtMtnSteve 

To achieve the steps you have outlined – extracting data from one workbook/sheet based on phone numbers and copying it to another workbook/sheet – you will need to use a combination of Excel functions and operations. While formulas cannot directly copy data across different workbooks and sheets, you can achieve this using Excel's built-in data manipulation functions and tools.

Here is a general step-by-step guide on how you might approach this task:

1. VLOOKUP or INDEX-MATCH: In your target workbook (W1S1), create columns next to the phone numbers where you want to copy the data (e.g., columns D, E, F for ID#, Last Name, First Name). In column D, you can use a VLOOKUP or INDEX-MATCH formula to search for the corresponding phone number in W2S2 and retrieve the ID#.

For example, in cell D2 of W1S1, you can use the formula:

1. =VLOOKUP(A2, 'Path\to\[W2.xlsx]Sheet2'!$A:$C, 2, FALSE)
2. This formula searches for the phone number in column A of W2S2 and retrieves the value from column B (ID#).
3. Repeat for Last Name and First Name: Use similar formulas for columns E and F to retrieve the Last Name and First Name from W2S2 based on the matching phone number.
4. Copy-Paste Values: Once you have the formulas in columns D, E, F of W1S1, copy these cells, then right-click on the corresponding range in W1S1 and choose "Paste Special" > "Values" to replace the formulas with their calculated values.
5. Repeat for Each Row: Drag down the formulas in columns D, E, F to cover all the rows in W1S1 where you want to extract data.

Please note that this approach assumes that you are manually triggering the calculations and data copying. Formulas in Excel cannot perform automatic actions like copy-pasting data between workbooks/sheets; they can only calculate and retrieve values.

If you need to automate the entire process, you might consider using VBA (Visual Basic for Applications), which is a programming language integrated with Excel. VBA can perform actions like opening workbooks, copying data, and pasting it in specified locations. However, using VBA involves more advanced concepts and is beyond the scope of a simple formula.

Remember to adjust the paths and sheet names ('Path\to[W2.xlsx]Sheet2') in the formulas to match your actual workbook and sheet names. The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

@NikolinoDE- Thank you for your help in saving me time. I will attempt your solution to see if it might save me some time.

I appreciate your time. Have a grand evening.

Steve