help with lookup for student schedules

[kingabraham3]

Hi all - first post. i'm trying to do a lookup based on two criterion. I've searched and seen multiple solutions, but have not been able to figure out how to modify to my situation.

In summary - I want to lookup a classroom number based on a two criteron: (1) student ID number, and (2) desired class period.

Any help would be great! Thanks!

The file can be downloaded here: http://sdrv.ms/PQgbZ7

Or, here is an attempt to copy in what the file looks like:
This below is the interface sheet. I enter the desired ID numbers in column A, and choose the period from the top. The name and grade populate from a simple vlookup formula. What I cannot figure out is how to get the correct subject and room number. The sheet with the data is sampled further below.

		Select Period >	1
IDs	First Name	Last Name	Grade	Subject	Room P1
1635676	John	Smith	9
6047865	Michael	Jordan	11

<tbody>
</tbody>

Here is the raw data:

StudentId	LastName	FirstName	Grade	Period	CourseName	RoomNumber
1635676	Smith	John	9	1	Algebra 1	3077
1635676	Smith	John	9	2	Intensive Math	3077
1635676	Smith	John	9	3	World History	3063
1635676	Smith	John	9	4	English 1	3080
1635676	Smith	John	9	5	Intensive Readi	3080
1635676	Smith	John	9	6	Pers/Car/Sch De	3053
1635676	Smith	John	9	7	Physical Sci	2068
1635676	Smith	John	9	8	Health Science	2104
6047865	Jordan	Michael	11	1	U.S. History	2073
6047865	Jordan	Michael	11	3	Eng Hon 3	3059
6047865	Jordan	Michael	11	4	Early Child 2	1127
6047865	Jordan	Michael	11	5	DE: Spanish I	2057
6047865	Jordan	Michael	11	6	Crit Think St S	1174
6047865	Jordan	Michael	11	7	Intensive Readi	3086
6047865	Jordan	Michael	11	8	Algebra 2	3073

<tbody>
</tbody>

 

[Robert Mika]

Hi all - first post. i'm trying to do a lookup based on two criterion. I've searched and seen multiple solutions, but have not been able to figure out how to modify to my situation.

In summary - I want to lookup a classroom number based on a two criteron: (1) student ID number, and (2) desired class period.

Any help would be great! Thanks!

The file can be downloaded here: http://sdrv.ms/PQgbZ7

Or, here is an attempt to copy in what the file looks like:
This below is the interface sheet. I enter the desired ID numbers in column A, and choose the period from the top. The name and grade populate from a simple vlookup formula. What I cannot figure out is how to get the correct subject and room number. The sheet with the data is sampled further below.

		Select Period >	1
IDs	First Name	Last Name	Grade	Subject	Room P1
1635676	John	Smith	9
6047865	Michael	Jordan	11

<tbody>
</tbody>

Here is the raw data:

StudentId	LastName	FirstName	Grade	Period	CourseName	RoomNumber
1635676	Smith	John	9	1	Algebra 1	3077
1635676	Smith	John	9	2	Intensive Math	3077
1635676	Smith	John	9	3	World History	3063
1635676	Smith	John	9	4	English 1	3080
1635676	Smith	John	9	5	Intensive Readi	3080
1635676	Smith	John	9	6	Pers/Car/Sch De	3053
1635676	Smith	John	9	7	Physical Sci	2068
1635676	Smith	John	9	8	Health Science	2104
6047865	Jordan	Michael	11	1	U.S. History	2073
6047865	Jordan	Michael	11	3	Eng Hon 3	3059
6047865	Jordan	Michael	11	4	Early Child 2	1127
6047865	Jordan	Michael	11	5	DE: Spanish I	2057
6047865	Jordan	Michael	11	6	Crit Think St S	1174
6047865	Jordan	Michael	11	7	Intensive Readi	3086
6047865	Jordan	Michael	11	8	Algebra 2	3073

<tbody>
</tbody>

Hello and welcome to Mr Excel:
Enter IDs

*	A	B	C	D	E	F
1	ENTER ID NUMBERS BELOW	*	Select Period >	1
2	IDs	First Name	Last Name	Grade	Subject	Room P1
3	1635676	John	Smith	9	Algebra 1	3077
4	6047865	Michael	Jordan	11	U.S. History	2073
5	*	*	*	*	*	*
6	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:82px;"><col style="width:73px;"><col style="width:87px;"><col style="width:44px;"><col style="width:114px;"><col style="width:59px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E3 {=INDEX(Schedules!F$2:F$16,MATCH($A3&$D3&$F$1,Schedules!$A$2:$A$167&Schedules!$D$2:$D$16&Schedules!$E$2:$E$16,0))} F3 {=INDEX(Schedules!G$2:G$16,MATCH($A3&$D3&$F$1,Schedules!$A$2:$A$167&Schedules!$D$2:$D$16&Schedules!$E$2:$E$16,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

=IFERROR(INDEX(Schedules!F$2:F$16,MATCH($A3&$D3&$F$1,Schedules!$A$2:$A$167&Schedules!$D$2:$D$16&Schedules!$E$2:$E$16,0)),"")

Confirm Control+Shift+Enter.
The additionnal function return empty cell if there is no match.

 

[kingabraham3]

Thanks! That works perfectly!

 

[Robert Mika]

Thanks! That works perfectly!

You are welcome.
Thanks for feedback