Help using complex lookup to populate table

[alexllap]

I'm not sure if this is even possible, but I have a table with approximately 4K rows, with each row listing a student and the grade that they received in a particular course.

	A	B	C	1D
1	Student #	Name	Course	Grade
2	12345	Jack	ENGL 101	3.3
3	12345	Jack	MUSIC 200	3.7
4	12346	Dan	ENGL 101	4
5	12346	Dan	FRENCH 105	3.9
6	12347	Sarah	ENGL 101	3.7
7	12347	Sarah	MUSIC 200	3.6
8	12348	Michelle	ENGL 105	3.8
9	12348	Michelle	ENGL 101	3.8

<tbody>
</tbody>

In another sheet, I have a row for each student, and columns with the courses that I need to make sure that they've taken.

	A	B	C	D
1	Student #	Name	ENGL 101	ENGL 105
2	12345	Jack
3	12346	Dan
4	12347	Sarah
5	12348	Michelle

<tbody>
</tbody>

Is there a formula or macro that can populate this second sheet with the grade that they received in each course? So it would look like this:

	A	B	C	D
1	Student #	Name	ENGL 101	ENGL 105
2	12345	Jack	3.3
3	12346	Dan	4
4	12347	Sarah	3.7
5	12348	Michelle	3.8	3.8

<tbody>
</tbody>

I've tried using VLOOKUP a few different ways, but I'm not very good with nested formulas, or formulas in general for that matter. Any help is greatly appreciated!

 

[MarcelBeug]

In the first sheet you can add a helper column with a concatenation of Student # and Course (with a delimiter in between), e.g. in cell E2: =A2&"|"&C2
Copy down for all rows.


Assuming the first sheet is "Sheet1", in the second sheet; in cell C2 enter: =IFERROR(INDEX(Sheet1!$D:$D,MATCH($A2&"|"&C$1,Sheet1!$E:$E,0)),"")
Copy to the right and down.

 

[JoeMo]

Copy the formulas in C2:D2 down. Assumes your main data are in Sheet1.

Excel Workbook
	A	B	C	D
1	Student #	Name	ENGL 101	ENGL 105
2	12345	Jack	3.3
3	12346	Dan	4
4	12347	Sarah	3.7
5	12348	Michelle	3.8
Sheet2