Match and combine data in two sheets

[antoniog436]

Hi,
I would like to combine two sheets/excell files which are as follows. In the first one, I have a list of students with their IDs (column A), names and last names (remaining columns). In the second one, I have the marks obtained by the students in a test mapped to their IDs (column A, student ID; column B, student marks). Not all the students took the test so that the file with the marks has less rows. So, I would like to combine the data into a single sheet/excell file depicting the students´ marks matched to their names and last names. Thanks in advance for any help.


PS. I am using MacOffice 2008

 

[JackDanIce]

Welcome to the board.

I think a VLOOKUP function will work fine if the IDs are all unique.

Something like:

=VLOOKUP(A1,'Sheet2!'$A$1:$B$200,2,0)

This link will detail how to use VLOOKUP if you've not used it before:
http://www.timeatlas.com/5_minute_tips/general/learning_vlookup_in_excel

 

[antoniog436]

Hi,
Thanks a lot for your reply. I guess I am doing something wrong because I tried your code and it did not work. I used it in the following example.

Sheet1
1 I
12 Fary
234 Esther
546 Matias
1234 Juan

Sheet2
12 7.2
234 8.1
546 7.5
1234 6.5

Then I pasted the code in column C of Sheet1 (Sheet1 and Sheet2 are on the same excell file) but nothing happens. I am sure I am missing something but what? Thanks in advance for any extra help. I will also check the site that you suggested because I know nothing about VLOOKUP.
Regards,

 

[JackDanIce]

For sake of how the tool I have that can post the example onto the board, I put it all on Sheet1, but it does work:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 8pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">ID</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold">ID</TD><TD style="FONT-WEIGHT: bold">Marks</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD>I</TD><TD style="BACKGROUND-COLOR: #ffff00">#N/A</TD><TD></TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">7.2</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Fary</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">7.2</TD><TD></TD><TD style="TEXT-ALIGN: right">234</TD><TD style="TEXT-ALIGN: right">8.1</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">234</TD><TD>Estger</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8.1</TD><TD></TD><TD style="TEXT-ALIGN: right">546</TD><TD style="TEXT-ALIGN: right">7.5</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">546</TD><TD>Matias</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">7.5</TD><TD></TD><TD style="TEXT-ALIGN: right">1234</TD><TD style="TEXT-ALIGN: right">6.5</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">1234</TD><TD>Juan</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6.5</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=VLOOKUP(A2,$E$2:$F$5,2,0)</TD></TR><TR><TD>C3</TD><TD>=VLOOKUP(A3,$E$2:$F$5,2,0)</TD></TR><TR><TD>C4</TD><TD>=VLOOKUP(A4,$E$2:$F$5,2,0)</TD></TR><TR><TD>C5</TD><TD>=VLOOKUP(A5,$E$2:$F$5,2,0)</TD></TR><TR><TD>C6</TD><TD>=VLOOKUP(A6,$E$2:$F$5,2,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Have you tried using the Excel Wizard Function? To adjust above formula to allow for Sheet2, change them to:

=VLOOKUP(A2,Sheet2!$A$2:$B$5,2,0)
=VLOOKUP(A3,Sheet2!$A$2:$B$5,2,0)
=VLOOKUP(A4,Sheet2!$A$2:$B$5,2,0)
=VLOOKUP(A5,Sheet2!$A$2:$B$5,2,0)
=VLOOKUP(A6,Sheet2!$A$2:$B$5,2,0)

 

[antoniog436]

Hi,
Just great! Thanks a lot.
Best regards,