• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!

 
Question
191 views

Combining data from two Excel worksheets, with one common field.

c19587 asked on

I have two columns of text and data in tab-1 of my Excel 2007 workbook.  Column “A” contains a list of unique alphanumeric names and column “B” contains a list of the alphanumeric IDs - one unique ID for each unique name.   The list will be several hundred rows long.

TAB-1

COLUMN A

COLUMN B

PEN RED

1234

COMPUTER BLUE

5678

 

In tab-2 of the same workbook, column “A” is empty and I have a list of unique IDs in column B, some of which are the same as the IDs on tab-1.  To the right of the IDs on tab-2, I have several columns of information for each unique ID. 

TAB-2

COLUMN A

COLUMN B

COLUMN C

COLUMN D

 

1234

RED

Pink

 

5678

BLUE

Purple

 

2121

BLACK

GRAY

 

3434

GREEN

LIME

 

How can I copy the data from column A tab-1, to the appropriate rows in column A tab-2?

In other words, how can I copy the hundreds of unique names from tab-1 to tab 2 and have each name be on the same rows as its partner ID #? 

I've never done anything like this before in Excel.  I'm confident it can be done, but I'm stumped as to how.

2 people had this question

Abuse history


The answered status icon Answer
Wox33 replied on
Assuming you have headers and data starts in row 2. Try this in A2 of tab-2 and fill down. Replace 99 with the actual number of rows you have.

=INDEX('tab-1'!A$2:A$99,(MATCH(B2,'tab-1'!B$2:B$99,0)))

or this to replace the errors where there is no match:

=IF(ISERROR(INDEX('tab-1'!A$2:A$99,(MATCH(B2,'tab-1'!B$2:B$99,0)))),"",INDEX('tab-1'!A$2:A$99,(MATCH(B2,'tab-1'!B$2:B$99,0))))

Replace tab-1 and tab-2 with Sheet1 and Sheet2 if you actually did not rename these.


Be the first person to mark this helpful

Abuse history


progress