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

 

[Skybluekid]

Try this:

Index Match Second or Third value

Hi All My mind is blank on this ... I am trying to use Index Match to find the 2nd or 3rd value from a data array (ie A1:B6) Col A contains names ie ABC, Col B has various numbers. I've tried this formula: =INDEX(B1:B6,MATCH(A12,A1:A6,FALSE)+1,1) However instead of referencing the second value...

www.mrexcel.com

 

[Mighty_Mitchell]

Try this:

Index Match Second or Third value

Hi All My mind is blank on this ... I am trying to use Index Match to find the 2nd or 3rd value from a data array (ie A1:B6) Col A contains names ie ABC, Col B has various numbers. I've tried this formula: =INDEX(B1:B6,MATCH(A12,A1:A6,FALSE)+1,1) However instead of referencing the second value...

www.mrexcel.com

Thank you,

I've already looked at this thread and still can't get it to work!

I've tried to use this formula
=INDEX(SpotcheckDatabase,SMALL(IF(SpotcheckDatabaseDriverName='Spot Check Report'!B7,ROW(CheckDate)),2))
This returned either #NUM! or #REF!

Spotcheckdatabase shows all my data
SpotcheckdatabaseDriverName is a column in my spotcheckdatabase table
Check date is a column in my spotcheckdatabase table
Spot Check Report B7 is the name of the driver I wish to use as a Match field.

Thank you

 

[Peter_SSs]

See if this does what you want to retrieve the second value
=INDEX(SpotcheckDatabase,AGGREGATE(15,6,(ROW(SpotcheckDatabaseDriverName)-ROW(INDEX(SpotcheckDatabaseDriverName,1))+1)/(SpotcheckDatabaseDriverName='Spot Check Report'!B7),2),6)

 

[Mighty_Mitchell]

Thank you Peter.
Unfortunately it has returned #NUM!

 

[Peter_SSs]

Unfortunately it has returned #NUM!

That would happen if 'Spot Check Report'!B7 did not occur at least twice in SpotcheckDatabaseDriverName (though there could also be other reasons)

If that is not the issue then perhaps you need to give us a small set of dummy data to work with as we are guessing just what it is like and how it is laid out. My signature block below has help with that.

 

[Marcelo Branco]

See if this example helps - getting John's 1st and 2nd scores

Book1
	A	B	C	D	E	F
1	Name	Score		Name	Score	Result
2	Anthony	10		John	1	9
3	John	9		John	2	7
4	Mary	11
5	Anthony	12
6	John	7
7	Mary	8
8	Anthony	9
9	John	8
10	Mary	9
Plan1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX(B$2:B$10,AGGREGATE(15,6,(ROW(B$2:B$10)-ROW(B$2)+1)/(A$2:A$10=D2),E2))

 

[Mighty_Mitchell]

The first image is my table.
The entire table is From cell A:AV (all rows down) is named SpotCheckDataBase
This table is on a tab named Spot Check Database
Column F is named CheckDate and Column G is named Result

The second image is the table I am populating. This is on a separate tab named Spot Check Report.
I have already populated the date of latest spot check and result by using the below formulae.
Latest Spot Check Date: =IFERROR(INDEX(SpotcheckDatabase,MATCH(B7,SpotcheckDatabaseDriverName,0),6),"")
Latest Result: =IFERROR(INDEX(SpotcheckDatabase,MATCH($B7,SpotcheckDatabaseDriverName,0),7),"")
I am struggling with the formula to enter into the date of previous spot check and result of previous spot checks.


I don't know what I am doing wrong. It's so frustrating!
Thank goodness for Mr Excel!

 

[Marcelo Branco]

Try something like this - i used ranges instead of table references

Data in Sheet1

Book1
	A	B	C	D	E	F	G
1	Driver	Inspector	Vehicle	Trailer	Location	Check Date	Result
2	Carole	Peter	FTE5000	AVR 1	Birmingham	01/11/2019	Pass
3	Jaqui	Peter	FTE6000	AVR15	Manchester	20/10/2019	Pass
4	John	Peter	FTE7000	N/A	London	10/10/2019	Pass
5	Carole	Peter	FTE5000	AVR 1	Sheffield	05/10/2019	Fail
6	John	Peter	FTE7000	N/A	Southhampton	16/09/2019	Fail
7	Carole	Peter	FTE5000	AVR 1	Birmingham	09/09/2019	Fail
8	Jaqui	Peter	FTE6000	AVR15	Leeds	01/09/2019	Pass
Sheet1

Results in Sheet2

Book1
	B	C	D	E	F	G	H
6	Driver	Date Latest	Spot Latest	Days	Date Previous	Result Previous	Days Between
7	Carole	01/11/2019	Pass		05/10/2019	Fail	27
8	Jaqui	20/10/2019	Pass		01/09/2019	Pass	49
9	John	10/10/2019	Pass		16/09/2019	Fail	24
Sheet2

Cell Formulas
Range	Formula
C7:C9	C7	=INDEX(Sheet1!F$2:F$8,MATCH(B7,Sheet1!A$2:A$8,0))
D7:D9	D7	=INDEX(Sheet1!G$2:G$8,MATCH(B7,Sheet1!A$2:A$8,0))
F7:F9	F7	=INDEX(Sheet1!F$2:F$8,AGGREGATE(15,6,(ROW(Sheet1!A$2:A$8)-ROW(Sheet1!A$2)+1)/(Sheet1!A$2:A$8=B7),2))
G7:G9	G7	=INDEX(Sheet1!G$2:G$8,AGGREGATE(15,6,(ROW(Sheet1!A$2:A$8)-ROW(Sheet1!A$2)+1)/(Sheet1!A$2:A$8=B7),2))
H7:H9	H7	=C7-F7

Hope this helps

M.

 

[Peter_SSs]

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)))