Index Match to return 2nd value

[Mighty_Mitchell]

I am trying to populate a table using formulae from a tab containing the data.
I have completed this for the latest set of information I require, but would also like to add a column to show the result previous to this. So we have the 2 most recent results appearing in the table.

I am happy with the formula I have used to obtain the 'most recent' detail, but I am struggling to complete the formula to show me the '2nd most recent' detail.

Most Recent: =IFERROR(INDEX(SpotcheckDatabase,MATCH(B7,SpotcheckDatabaseDriverName,0),6),"")

What do I need to do to change this formula to show me the second most recent?

Thank you in advance

 

[Marcelo Branco]

Remark
The formulas above assume dates in descending order (column F)

M.

 

[Mighty_Mitchell]

Thanks for the additional sample data but for the future, you will get much better response if you show data that can be copied, rather than just an image from which helpers would have to manually type the data to test. Most helpers are not interested in manually typing out the sample data Again, my signature block below has help with that.

I have assumed that a driver will not take the test twice on the same day. If that can happen the formulas would need adjusting.
Note that I have provided an alternative formula for C7 & D7 as well.

Book1
	B	C	D	E	F
7	Carole	1/11/2019	Pass	5/10/2019	Fail
Spot Check Report

Cell Formulas
Range	Formula
C7	C7	=IFERROR(AGGREGATE(14,6,CheckDate/(SpotCheckDatabaseDriverName=B7),1),"")
D7	D7	=IF(C7="","",INDEX(Result,AGGREGATE(15,6,(ROW(Result)-ROW(INDEX(Result,1))+1)/((SpotCheckDatabaseDriverName=B7)*(CheckDate=C7)),1)))
E7	E7	=IFERROR(AGGREGATE(14,6,CheckDate/(SpotCheckDatabaseDriverName=B7),2),"")
F7	F7	=IF(E7="","",INDEX(Result,AGGREGATE(15,6,(ROW(Result)-ROW(INDEX(Result,1))+1)/((SpotCheckDatabaseDriverName=B7)*(CheckDate=E7)),1)))

Thank you so much, I am new to this forum. I will take a look at how to provide you with the actual data rather than an image

The top three formula work, but the formula in cell F7 returns #NUM!

The dates in the database are in order of newest to oldest spotcheck dates.

Thank you

 

[Peter_SSs]

the formula in cell F7 returns #NUM!

I haven't been able to reproduce that error.

1. Are you using the formula on the sample data shown in post 8?
   - If so which driver name is producing that error
   - If not, can you try it on that sample data exactly as shown?
2. Did you copy the formula from the board, or type it manually? If typed manually, try copy/paste to ensure the formula is identical to that psoted.
3. If still unresolved, try to post a small set of sample data that produces the error so we can copy/test. If you are unable to use the XL2BB forum tool, you could upload the small sample file (eg to Dropbox, OneDrive etc) and provide a shared link here.