need help populating table using lookup or index function

**vt78cruiser** · 07-25-2013, 09:38 AM

Please see attached dummy spreadsheet for details.

I am trying to get the "Camp List" sheet to populate using information from the "Schedules" sheet. Please imagine that I have thousands of students and hundreds of teachers which is why something like this is necessary.

On the "Camp List" sheet, Cell B2 should call information from the "Schedules" sheet, searching for that student, looking up which class qualifies as English in column F, "Class Type", and returning the corresponding "Crs Title" from that row. I am only a novice with Excel and have tried a combination of Lookup and IF statements but have not found how to combine the two.

If working correctly, I could use this to populate the whole "Camp List" sheet, showing me that 'Jones, Tom' is taking AP English Lit/Comp S1 for this English class and that 'Jockel' is his teacher.

**benishiryo** · 07-25-2013, 10:00 AM

hi vt78cruiser, welcome to the forum. could you standardize the subject spelling in the 2 sheets? so C3 should also read "English Teacher" instead of just "Eng". then in B2, try this array formula:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

in E3 & E4, you'll see 0. that's because there is such a student & subject, but "Teacher" field was blank. to hide this, format all your cells to Custom:
General;;