# if it matches, then do a vlookup

#### ndjohnson04

##### New Member
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.

Last edited:

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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),"")

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?

Hey John,

See if this works.

Excel 2007
ABCDEFGH
1Student ID2010 CSAP Math GR 052011 CSAP Math GR 06Student IDTestScaleScore
211111555#N/A111112010 CSAP Math GR 05555
3222222011 CSAP Math GR 06555
Sheet1
Cell Formulas
RangeFormula
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.

Hey John,

See if this works.

Excel 2007
AB
CDEFGH
1Student ID2010 CSAP Math GR 052011 CSAP Math GR 06Student IDTestScaleScore
211111555#N/A111112010 CSAP Math GR 05555
3222222011 CSAP Math GR 06555

</tbody>
Sheet1

Array Formulas
CellFormula
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)}

</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
AB
CDEFGH
1Student ID2010 CSAP Math GR 052010 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
11111555#N/A
3
22222

</tbody>
Sheet1
Excel 2007
AB
CDEFGH
1Student IDTest
ScaleScore
2
11111
2010 CSAP Math GR 05
555
322222
2010 CSAP Writing GR 05
583

</tbody>
Sheet2

John,

Muz.

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.

Muz,

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

Replies
1
Views
323
Replies
3
Views
89
Replies
7
Views
564
Replies
7
Views
425
Replies
15
Views
976

### Forum statistics

1,203,490
Messages
6,055,726
Members
444,814
Latest member
AutomateDifficulty

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back