Index Match to return 2nd value

[Mighty_Mitchell]

I've been struggling with this one...

I have a spreadsheet full of data. The table (table named 'EmployeeCheckDatabase') consists of employee names in column A (column named 'EmployeeName') and a date of an employee check in column F (column named 'CheckDate') - column F being the sixth column in my table. The data on the spreadsheet is in order of most recent at the top.

On another spreadsheet within the same workbook I am producing a table which is to be populated by the data table on the above mentioned spreadsheet (EmployeeCheckDatabase).

My table shows the employee names in the first column and then 2 more columns, one for the most recent check date and one for the 2nd recent check date.

To achieve the most recent check date I have used the following formulae:

=INDEX(EmployeeCheckDatabase,MATCH(Lookup value being employee name,Employeename,0)6)

What formula would I use to find the second recent check date?

Thank you in advance

 

[tyija1995]

Glad it works! Have a good one

 

[jasonb75]

As dates are numeric, you don't need to use index, just pull it directly.

=IFERROR(AGGREGATE(15,6,CheckDate/(EmployeeName=lookupvalue),2),"")

If you have excel 2019 or office 365 then you could also do it with MAXIFS, which might be more efficient with a large table of data.

=IFERROR(1/(1/MAXIFS(CheckDate,EmployeeName,lookupvalue,CheckDate,"<"&B2)),"")

Where B2 refers to the cell containing the formula for the first date.

 

[Peter_SSs]

As dates are numeric, you don't need to use index, just pull it directly.

That is the second time recently that I overlooked that.
(I also missed that column F was a named range)

=IFERROR(AGGREGATE(15,6,CheckDate/(EmployeeName=lookupvalue),2),"")

Though because the OP wants the second most recent date (ie second largest) wouldn't this need to be?

=IFERROR(AGGREGATE(14,6,CheckDate/(EmployeeName=lookupvalue),2),"")

 

[jasonb75]

Good catch, Peter!
I keep getting that wrong, I find it counter intuitive having to remember a smaller number for large also, I find that I use it with small far more often than with large, so brain goes into auto pilot.

 

[Peter_SSs]

Good catch, Peter!
I keep getting that wrong, I find it counter intuitive having to remember a smaller number for large

I agree! every time I use the 14/15 arguments I ask myself why it wouldn't have been programmed the other way around?

 

[jasonb75]

I wonder why it wasn't programmed with the names of the functions as text instead, similar to the first argument of the CELL() function?

Small and large are the only ones I remember, anything else, I have to check the list each time.