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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]1D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student #[/TD]
[TD]Name[/TD]
[TD]Course[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]Jack[/TD]
[TD]ENGL 101[/TD]
[TD]3.3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12345[/TD]
[TD]Jack[/TD]
[TD]MUSIC 200[/TD]
[TD]3.7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12346[/TD]
[TD]Dan[/TD]
[TD]ENGL 101[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12346[/TD]
[TD]Dan[/TD]
[TD]FRENCH 105[/TD]
[TD]3.9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]12347[/TD]
[TD]Sarah[/TD]
[TD]ENGL 101[/TD]
[TD]3.7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]12347[/TD]
[TD]Sarah[/TD]
[TD]MUSIC 200[/TD]
[TD]3.6[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]12348[/TD]
[TD]Michelle[/TD]
[TD]ENGL 105[/TD]
[TD]3.8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]12348[/TD]
[TD]Michelle[/TD]
[TD]ENGL 101[/TD]
[TD]3.8[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student #[/TD]
[TD]Name[/TD]
[TD]ENGL 101[/TD]
[TD]ENGL 105[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12346[/TD]
[TD]Dan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12347[/TD]
[TD]Sarah[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12348[/TD]
[TD]Michelle[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student #[/TD]
[TD]Name[/TD]
[TD]ENGL 101[/TD]
[TD]ENGL 105[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]Jack[/TD]
[TD]3.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12346[/TD]
[TD]Dan[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12347[/TD]
[TD]Sarah[/TD]
[TD]3.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12348[/TD]
[TD]Michelle[/TD]
[TD]3.8[/TD]
[TD]3.8[/TD]
[/TR]
</tbody>[/TABLE]

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