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

 

[jamescooper]

This is the basic formula,

remember to press CTRL + ALT + ENTER

=INDEX(B1:B6,SMALL(IF(A1:A6=A12,ROW(B1:B6)-ROW(B1)+1),2))

 

[tyija1995]

It sounds like you want an Nth index match where N = 2 in this case!

=INDEX(F:F,SMALL(IF(A:A="employee_name",ROW(A:A)-ROW(INDEX(A:A,1,1))+1),2))

Ctrl+Shift+Entered (CSE) - this is an ARRAY formula. The 2 in bold above represents the Nth match

 

[Peter_SSs]

If you don't want to have to use the Ctrl+Shift+enter confirmation, then you can try

=INDEX(EmployeeCheckDatabase,AGGREGATE(15,6,(ROW(Employeename)-ROW(INDEX(Employeename,1))+1)/(Employeename=Lookup value being employee name),2),6)

 

[Mighty_Mitchell]

It sounds like you want an Nth index match where N = 2 in this case!

=INDEX(F:F,SMALL(IF(A:A="employee_name",ROW(A:A)-ROW(INDEX(A:A,1,1))+1),2))

Ctrl+Shift+Entered (CSE) - this is an ARRAY formula. The 2 in bold above represents the Nth match

Thank you so much!
I now have another problem...
In my first formula I added IF(ERROR to the beginning and ,"") to the end so that a blank cell would appear if there had only been one test.
This does not work in the second formula (is this because of the CSE?). Is there a way I can produce a blank cell if no second test date?

Thank you

 

[tyija1995]

Thank you so much!
I now have another problem...
In my first formula I added IF(ERROR to the beginning and ,"") to the end so that a blank cell would appear if there had only been one test.
This does not work in the second formula (is this because of the CSE?). Is there a way I can produce a blank cell if no second test date?

Thank you

Hey, did you re-enter with CSE again? If you manually updated with the nested IFERROR with a regular enter it would have converted the array formula back to a normal formula - every adjustment needs to be CSE

 

[Mighty_Mitchell]

Hey, did you re-enter with CSE again? If you manually updated with the nested IFERROR with a regular enter it would have converted the array formula back to a normal formula - every adjustment needs to be CSE

Hi, Yes I did CSE (first time I've ever used that!)
The formula is now encase with { and }

See below, the table showing the top 3 employees. Only 1 has been tested twice leaving the top 2 employees with a #NUM !
Since learning the IF(ERROR ….. ,"") this has become a pet hate of mine

Date of Previous Spot Check	Result of Previous Spot Check
#NUM !	#NUM !
#NUM !	#NUM !
04/07/2019	Fail

<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

[tyija1995]

If you don't want to have to use the Ctrl+Shift+enter confirmation, then you can try

=INDEX(EmployeeCheckDatabase,AGGREGATE(15,6,(ROW(Employeename)-ROW(INDEX(Employeename,1))+1)/(Employeename=Lookup value being employee name),2),6)

Fantastic! I was unaware that Nth index match could be performed without array formula :D Nice work Peter! Had a little play with this formula myself

 

[tyija1995]

Hi, Yes I did CSE (first time I've ever used that!)
The formula is now encase with { and }

See below, the table showing the top 3 employees. Only 1 has been tested twice leaving the top 2 employees with a #NUM !
Since learning the IF(ERROR ….. ,"") this has become a pet hate of mine

Are you putting it as IF(ERROR ...) or IFERROR? The syntax should be the original formula, prefixed with IFERROR and suffixed with a comma and empty string + closing parenthesis like: IFERROR( *formula goes here* , "")

To summarise: Does your formula look like this:
IFERROR(INDEX(F:F,SMALL(IF(A:A="employee_name",ROW(A:A)-ROW(INDEX(A:A,1,1))+1),2)),"")

Where the red font represents the new changes to the formula

 

[Mighty_Mitchell]

Are you putting it as IF(ERROR ...) or IFERROR? The syntax should be the original formula, prefixed with IFERROR and suffixed with a comma and empty string + closing parenthesis like: IFERROR( *formula goes here* , "")

To summarise: Does your formula look like this:
IFERROR(INDEX(F:F,SMALL(IF(A:A="employee_name",ROW(A:A)-ROW(INDEX(A:A,1,1))+1),2)),"")

Where the red font represents the new changes to the formula

It does now!
Thank you