# How to line up data based on a single identifier?March 24, 2011 1:46 PM   Subscribe

MS Excel: All of my subjects took two tests and Scores 1 & 2 are in one sheet. Some of my subjects took an additional two tests, and Scores 3 & 4 are in another sheet. How can I merge the two sheets and align the rows so that each subject has all four scores in individual cells in a single row?

The first sheet looks like this: all subjects have a unique 4-digit ID and numeric Scores 1 & 2.

1 4056 4 6
2 4089 6 8
3 4192 7 8
4 4193 2 5
5 4202 3 5

The second sheet looks like this. The subjects are the same people, but only some of the people from the first sheet completed Scores 3 & 4.

1 4056 8 9
2 4192 2 4
3 4202 6 8

What I want is a single sheet in which the rows are aligned by subject ID, and all the scores are in their own cells.

1 4056 4 6 8 9
2 4089 6 8
3 4192 7 8 2 4
4 4193 2 5
5 4202 3 5 6 8

I thought this would be easier but I'm stumped. Any ideas?
posted by oceanmorning to Computers & Internet (6 answers total) 1 user marked this as a favorite

Best answer: Presuming your first sheet data fills columns A:C, copy your second lot into, say, columns AA:AC.
Now, in cell D1 enter:

=VLOOKUP(\$A1,AA:AC,2,0)

...and now fill across and down.
posted by pompomtom at 1:50 PM on March 24, 2011 [1 favorite]

Best answer: (gaaah, sorry, don't fill across. In E1 put

=VLOOKUP(\$A1,AA:AC,3,0)

..and then fill down)
posted by pompomtom at 1:51 PM on March 24, 2011 [1 favorite]

Response by poster: Fantastic. Thank you. I knew there must be a straightforward answer!
posted by oceanmorning at 2:02 PM on March 24, 2011

pompomtom has it. You are looking for vlookup.

It is essentially filling the 3rd and 4th test cells with info from that set of data where the ID's match.
posted by milqman at 2:03 PM on March 24, 2011

Best answer: If you would like to avoid seeing #N/A where subjects did not take test 3 or 4, you can use the same VLOOKUP, and display a blank cell if no results are found.

=IF(ISNA(VLOOKUP(\$A1,AA:AC,2,0)),"",VLOOKUP(\$A1,AA:AC,2,0))

The ISNA function evaluates the VLOOKUP result. If it is "#N/A", indicating that the Lookup_Value was not found in the Table_array, the cell shows a null. If false, the VLOOKUP result is shown.
posted by Snerd at 5:36 PM on March 24, 2011 [1 favorite]

Response by poster: Wow, I have learned two tricks here that will save me HOURS of fiddly formatting work in the future! Thanks!
posted by oceanmorning at 6:15 PM on March 24, 2011

« Older Will Symantec PC anywhere work for me?   |   Homewreckers Newer »