if it matches, then do a vlookup

[ndjohnson04]

OKay, I have been trying to figure a formula out for this and need some help. I have 2 sheets. The first sheet contains students ids and columns for individual test scores (currently blank). The second sheet contains columns with student ids, test name, scores.
Sheet 1
Student ID-----2010 CSAP Math GR 05-----2011 CSAP Math GR 06
1211111-------currently blank-------------Currently blank


Sheet 2
Student ID------Test------------------------ScaleScore
11111-----------2010 CSAP Math GR 05------ 555
22222-----------2011 CSAP Math GR 06------555




If I do just a vlookup =VLOOKUP([student.studentNumber], ICTCAP, 6,FALSE), it just gives me the scale core of the first one that shows up for that student ID. However what I would like it do that it just report back the numbers if the test name on sheet 1 appears in the test of sheet 2. Hope that makes sense.

 

[ad1234]

Your last paragraph is little tricky to understand (it is Friday pm!) but... Try:

=IF(Sheet1!A3=Sheet2!A3,VLOOKUP([student.studentNumber], ICTCAP, 6,FALSE),"")

 

[ndjohnson04]

Your last paragraph is little tricky to understand (it is Friday pm!) but... Try:

=IF(Sheet1!A3=Sheet2!A3,VLOOKUP([student.studentNumber], ICTCAP, 6,FALSE),"")

nope didn't work.

Basically Iwant it to pull the test scores for the test that appears in the header for the students. So the Vlookup portion works, what I need it to do is filter through the test column of sheet 2 and only return the values of the test that appears in the header of sheet 1. Does that help clear it up a little?

 

[Sam_D_Ben]

Hey John,

See if this works.

Excel 2007
	A	B	C	D	E	F	G	H
1	Student ID	2010 CSAP Math GR 05	2011 CSAP Math GR 06			Student ID	Test	ScaleScore
2	11111	555	#N/A			11111	2010 CSAP Math GR 05	555
3						22222	2011 CSAP Math GR 06	555
Sheet1

Cell Formulas
Range	Formula
B2	{=INDEX($F$1:$H$3,MATCH(1,(A2=$F$1:$F$3)*(B$1=$G$1:$G$3),0),3)}
C2	{=INDEX($F$1:$H$3,MATCH(1,(A2=$F$1:$F$3)*(C$1=$G$1:$G$3),0),3)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks,

Muz.

 

[ndjohnson04]

Hey John,

See if this works.

Excel 2007

	A	B	C	D	E	F	G	H
1	Student ID	2010 CSAP Math GR 05	2011 CSAP Math GR 06			Student ID	Test	ScaleScore
2	11111	555	#N/A			11111	2010 CSAP Math GR 05	555
3						22222	2011 CSAP Math GR 06	555

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B2 {=INDEX($F$1:$H$3,MATCH(1,(A2=$F$1:$F$3)*(B$1=$G$1:$G$3),0),3)} C2 {=INDEX($F$1:$H$3,MATCH(1,(A2=$F$1:$F$3)*(C$1=$G$1:$G$3),0),3)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks,

Muz.

Still no luck.
This is what my formula looked like when I put it in.
{=INDEX(Sheet2!$A$2:$C$3900,MATCH(1,(A2=Sheet2!$A$1:$A$3900)*(B$1=Sheet2!$B$1:$B$3900),0),3)}

what you had in F to H, I have on a different sheet.

Excel 2007

	A	B	C	D	E	F	G	H
1	Student ID	2010 CSAP Math GR 05	2010 CSAP Writing GR 05	<tbody> <td style="px"font-weight:" bold;border-top:="" 1px="" solid="" black;border-right:="" black;border-bottom:="" black;border-left:="" black;color:="" #333333;background-color:="" #ffff00;;"="">2012 CSAP Math GR 07 </td> </tbody>
2	11111	555	#N/A
3	22222

<colgroup><col style="px"width:" 25pxpx"=""><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1
​

Excel 2007

	A	B	C	D	E	F	G	H
1	Student ID	Test	ScaleScore
2	11111	2010 CSAP Math GR 05	555
3	22222	2010 CSAP Writing GR 05	583

<colgroup><col style="px"width:" 25pxpx"=""><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2
​

 

[Sam_D_Ben]

John,

Why don't you upload your file.

Muz.

 

[Sam_D_Ben]

John,

try this in Sheet1 Cell B2.

{=INDEX(Sheet2!A1:C3,MATCH(1,(Sheet1!A2=Sheet2!A1:A3)*(Sheet1!B1=Sheet2!B1:B3),0),3)}

Muz.

 

[ndjohnson04]

Muz,

How can I upload the file? The formula above did not work either.

 

[Sam_D_Ben]

Try Gmail Dropbox to upload your file and share its link here.

 

[ndjohnson04]

Here is the DropBox link
https://www.dropbox.com/sh/zpgxoq1epgxd18b/oLdRUSxnpR