Look up Last Date Attended and return date (column header) without VBA

[TeacherGriff]

I keep attendance records in my gradebook in a separate sheet. The date of attendance is in the columns (with the specific day's date in row 2) and the students' attendance is recorded with either a 1 (absent) or 0 (present) in the column starting in column D for the first day of class, so that I can record the number of absences by using a simple total calculation across a student's attendance row.

I use a separate sheet in the workbook to give me an "Individual Student View" so that I can discuss grades with my student without them being able to see the other students' grade information.

What I need is a formula for the "Individual Student View" sheet that, given the student's name (which is recorded in column A and B, Last name and First name) will look at the "Attendance Roster" sheet, find the student by their name in columns A and B, find the last 0 in that row, and return the date for that row-by-column location in the column header in row 2 - their last date attended.

So, for example, if John Smith has their last 0 in column F, the formula should return "2/20/16."

The area that contains the students' names is A3:B37; their attendance information is located in D3:S37; the area that contains the dates is D2:S2.

Any help appreciated. This is an .xlsx file, and there are certain functions that I need which only work if it's .xlsx, so I need a non-VBA solution.

I am using Microsoft Excel for Mac 2011, v. 14.6.1; and Mac OS X 10.11.3, if that matters.

 

[Snakehips]

TeacherGriff,

Not sure how this fits with Excel for Mac2011 but is ok Excel 2007

For ease or reading I have defined some named ranges

Dates = D2:S2 LastName = A3:A37 FirstName = B3:B37 Attends = D3:S37

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	Last	First		18/02	19/02	20/02	21/02	22/02	23/02	24/02	25/02	26/02	27/02	28/02	29/02	01/03	02/03	03/03	04/03
3	Thumb	Tom		0	0	0	0	0	0	1	0
4	Flintstone	Fred		0	0	0	1	1	0	0	1
5	Mouse	Mickey		0	0	0	1	1	1	1	1
Attendance Roster

Try the following Array (CSE) formula...

Excel Workbook
	A	B	C
2	Last	First	Last Attend
3	Flintstone	Fred	24/02/2016
ISV

Hope that helps.