Creating Attendance sheet with vlookup??

[1234kay]

Hi there,

I'm hoping someone can help me out. I am trying to make a Summary sheet that contains a formula in column B (starting at B5) that populates the current students listed on the StudentInfo sheet depending on the teacher selected in cell M1 on the Summary Sheet. I have tried using vlookups with if statements and also index formulas but I'm not having much luck. If anyone can help me out, it would be greatly appreciated. Examples of both sheets are below.

THANKS!!
1234kay

Summary Sheet

 

[pvr928]

Hi 1234kay

Post what you've attempted so far and I'm sure someone will be able to help you.

Cheers

pvr928

 

[1234kay]

The current array formula I'm using is:
=IF(ISERROR(INDEX(Students!$A$2:$C$172,SMALL(IF(Students!$A$1:$A$200=$W$1,ROW(Students!$A$1:$A$200)),ROW(Students!1:1)),3)),"",INDEX(Students!$A$2:$C$172,SMALL(IF(Students!$A$1:$A$200=$W$1,ROW(Students!$A$1:$A$200)),ROW(Students!1:1)),3)) which is in Column B. however I'm not sure where to include the if statement to check Column B on the Student Information sheet to see if they are a current student.

 

[Finalfight40]

Found this, you should be able to adjust to your situation:

https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

 

[1234kay]

Thanks for the information, Finalfight, but that is where I pulled the formula from and modified it to work with my current data.
I still didn't find how I can include the if to find out if the student is current or not.

Found this, you should be able to adjust to your situation:

https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

 

[Finalfight40]

Ahh didnt read that part, my bad.

Maybe this link and you could adjust:

https://www.get-digital-help.com/20...t-list-where-other-columns-meet-two-criteria/

Sorry for just posting links but its nearly time to log off and grab the train.