Thanks Ali!
But let me clarify what I was trying to get in one cell.
The way I labeled the columns in my example is confusing.
Instead of "Latest Absent DA" or "Latest Communication DA" (so on so forth) I should have written "Latest Absent DA LEVEL" and "Latest Communication DA LEVEL", etc, thus the need for the INDEX(Match.
I don't want to return the date, I want to return the Level associated with the date.
So I want something that is Indexing the Level (Verbal, Final, or Written?) and matching it to the employee name, the disciplinary type ("absent" "communication" or "tardy") and the latest date that this DA was received based on the type of DA indicated in the column
So like for the "Absent Column I think it would be something like:
INDEX(THE LEVEL COLUMN on the disciplinary action sheet,MATCH(1(EMPLOYEE NAME on disciplinary action sheet=NAME IN 1ST CELL IM REFERENCING) * (DISCIPLINARY ACTION COLUMN on the disciplinary action sheet="Absent") * (MAX(IF(NAMES IN COLUMN A on the disciplinary action sheet=NAME IN FIRST CELL IM REFERENCING,IF(DISCIPLINARY ACTION LEVEL COLUMN on the disciplinary action sheet="Absent", DATES LISTED IN COLUMN on the disciplinary action sheet),0))
But I cannot get this formula to work when I try to incorporate MAX(IF(IF into the index(match with multiple criteria.
It's starting to look like a science project.
Bookmarks