Returning n-th LARGEST DATE mult. criteria

[cparks]

I've tried LARGE/IF, INDEX/MATCH, MAXIFS functions and they don't quite get what I need or I need more criteria to enter and I just dont know how to properly throw it in there so...

I have two dates, both are days that a particular employee showed up and had a recorded job. One is the most recent day an employee did RECORDED work, the other is the next previous day an employee did RECORDED work.

I'm having an issue with getting the NEXT PREVIOUS day an employee did recorded work. There are days when employees show up for work but we dont record what they do. I need the recorded work.

Please see attachment of example.

 

[AliGW]

Try this:

=MAXIFS(Sheet2!$B:$B,Sheet2!$A:$A,Sheet1!A2,Sheet2!$C:$C,"PRESENT",Sheet2!$D:$D,">0",Sheet2!$B:$B,"<>"&B2)

 

[cparks]

SO SORRY Im just now getting back to you! My computer was acting retarded slow and it was next to impossible x's INFINITY to get it to do anything.

Your help works flawlessly! Thank you very much for the help! AGain, my apologies...

 

[AliGW]

You're welcome - glad to have helped!

 

[cparks]

You're welcome - glad to have helped!

Well maybe you can help me out on this one problem I incurred

Having an issue with using some of my "index/match" formulas. When I use my named ranges or when i select a larger range (column) than what that particular column visibly shows, I either get "0" or nothing at all...aside from using either IFERROR, ISBLANK, etc.

I know (or at least Im fairly certain) that when using lookup type formulas each array/criteria range needs to be the same amount of cells.
What gets me is when I select JUST the populated cells (i.e. visible), then the index/match formula works.

What am I missing?

 

[GraH - Guido]

Hi,
Without seeing your file, it is not simple to answer your question correctly.
In general, your match range must be within the index range. Otherwise you might return a position (via match) that is outside your index range.
from Excel help:
Array Required. A range of cells or an array constant
Row_num and Column_num must point to a cell within array; otherwise, INDEX returns the #REF! error value.
You can use named ranges.

Also it is a best practise to open a new thread if you have a new question... It might help others who have the same question.