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

TeacherGriff

New Member
Joined
Mar 19, 2016
Messages
1
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,554
Messages
5,548,719
Members
410,867
Latest member
Dhanas
Top