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

#### TeacherGriff

##### New Member
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.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Snakehips

##### Well-known Member
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
ABCDEFGHIJKLMNOPQRS
2LastFirst18/0219/0220/0221/0222/0223/0224/0225/0226/0227/0228/0229/0201/0302/0303/0304/03
3ThumbTom00000010
4FlintstoneFred00011001
5MouseMickey00011111
Attendance Roster

Try the following Array (CSE) formula...
Excel Workbook
ABC
2LastFirstLast Attend
3FlintstoneFred24/02/2016
ISV

Hope that helps.

Replies
5
Views
172
Replies
0
Views
60
Replies
1
Views
275
Replies
1
Views
125
Replies
3
Views
212