Use Excel to match data from a cell on a worksheet, then copy data from its neighboring column and paste on a different worksheet

Question

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.

Answer 1

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))
 )