2

I have a spreadsheet with multiple names and phone numbers next to them in each Sheet 1 - Sheet 5.

Each Sheet has about 900 names on column A and their associated phone numbers next to them on column B, on all five tab.

There is a 6th tab with column A with only phone numbers. I would like to create a formula or VBA if needed, that will search and try to match the phone numbers in the 6th tab in column A and match on the other 5 tabs. Then I would like to have that same formula or VBA and copy the name on that respective tab from the neighboring Column A and paste that name into the 6th tab onto column B.

I know it can be done. I just don't have that know how. :(

I have updated with pictures. As you can see on the first picture I only have phone numbers on a specific worksheet called ATT.

The second picture shows both the numbers and carrier name and person name on a worksheet called BlackBerries.

Both are on the same workbook. I want the name assigned to that phone number from that worksheet named BlackBerries and inserted into the worksheet called ATT.

Again, that I would like to add that the search and match will be between at least 5 worksheets all with similiar names Blackberries, BB2, BB3, BB4 ...etc.

They are all the same format.

enter image description here

enter image description here

  • Will phone numbers have more than one name? What have you tried already? – Raystafarian Jul 21 '15 at 11:44
  • I have tried and nothing yet....let me show you below on what I am trying to do. – Saul Gatica Jul 21 '15 at 18:15
  • edit your post to include additional information – Raystafarian Jul 21 '15 at 18:15
0

You can do this with a formula.

Do an index/match lookup through each sheet. If the sheet does not contain the number it is cached by the IFERROR and you try to look in the next sheet.

Below formula looks through all 5 worksheets :) Paste it into cell B2 in sheet ATT and copy it down...

=IFERROR(
    IFERROR(
        IFERROR(
            IFERROR(INDEX(Sheet1!H:H;MATCH(ATT!A2;Sheet1!B:B;0));
                INDEX(Sheet2!H:H;MATCH(ATT!A2;Sheet2!B:B;0))
            );
            INDEX(Sheet3!H:H;MATCH(ATT!A2;Sheet3!B:B;0))
        );
    INDEX(Sheet4!H:H;MATCH(ATT!A2;Sheet4!B:B;0))
    );
    INDEX(Sheet5!H:H;MATCH(ATT!A2;Sheet5!B:B;0))
 )

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.