Pulling data from another sheet based on specific criteria

[ExcelMonkey89]

Salutations!

I am trying to compile a summary sheet on information regarding the placement of camera-traps at different locations at an ecological research field site. We have multiple camera-trapping stations, each of which has two positions that a camera-trap can be placed in. These stations are serviced on an irregular basis (i.e. the dates are essentially random) and with each service, information about the camera-trap's location is recorded. There are already 3950 service records (covering 138 camera-traps used on-and-off across 29 stations and 58 positions over 11 years) and the database is growing, hence I want to summarise it. The data which I wish to summarise is on a worksheet (Sheet 1) and looks as follows:

	A​	B​	C​	D​
1	Date serviced	Station number	Position number	Camera-trap
2	11/11/2016	2​	4​	15
3	17/11/2016	7​	13​	13
4	26/11/2016	2​	4​	15
5	28/11/2016	11​	21​	2
6	01/12/2016	2​	4​	15
7	03/12/2016	19​	38​	11
8	04/12/2016	7​	13​	13
9	05/12/2016	13​	26​	13
10	12/12/2016	11​	21​	2
11	14/12/2016	2​	4​	15
12	17/12/2016	7​	13​	13
13	03/01/2017	8​	15​	2

For each camera-trap unit, there are multiple dates when the camera-trap was checked in the field and the Station number and Position were recorded. I am trying to summarise these records on a separate summary sheet to show the last location that the camera-trap was used (i.e. its most recent station). These data should be summarised on a separate sheet (Sheet 2) which has the following structure:

	A​	B​	C​	D​
1	Camera-trap	Last used/serviced	Most recent station	Most recent position
2	1	​
3	2	​
4	3	​
5	4	​
6	5​	​

So, using the example presented in above, for Camera-trap 2, the 'Last used/serviced' would be '03/01/2017', the 'Most recent station' should be '8' and 'Most recent position' should be '15'. I have figured out a formula for the 'Last used/serviced' which works but I need help with the formulae for the 'Most recent station' and 'Most recent position' (I realise that these formulae will be identical but with difference reference ranges, so if you can assist with only the 'Most recent station' one, I will adapt it for the 'Most recent position').

Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
Which version of Excel are you actually using? It can't really be all those you have listed.

 

[ExcelMonkey89]

Hi & welcome to MrExcel.
Which version of Excel are you actually using? It can't really be all those you have listed.

You are correct. I am using Excel 2016 but I listed several versions because the file will need to work on multiple machines and I know that we only have up to Office 2016.

 

[Fluff]

So does this need to work with 2003 or older or just 2016?

 

[Fluff]

This will work for 2010 onwards

+Fluff 1.xlsm
	A	B	C	D
1	Date serviced	Station number	Position number	Camera-trap
2	11/11/2016	2	4	15
3	17/11/2016	7	13	3
4	26/11/2016	2	4	15
5	28/11/2016	11	21	2
6	01/12/2016	2	4	15
7	03/12/2016	19	38	11
8	04/12/2016	7	13	13
9	05/12/2016	13	26	13
10	12/12/2016	11	21	2
11	14/12/2016	2	4	15
12	17/12/2016	7	13	3
13	03/01/2017	8	15	2
Sheet1

+Fluff 1.xlsm
	A	B	C	D
1	Camera-trap	Last used/serviced	Most recent station	Most recent position
2	1
3	2	03/01/2017	8	15
4	3	17/12/2016	7	13
5	4
6	5
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(AGGREGATE(14,6,Sheet1!$A$2:$A$13/(Sheet1!$D$2:$D$13=A2),1),"")
C2:D6	C2	=IFERROR(INDEX(Sheet1!B$2:B$13,AGGREGATE(14,6,(ROW(Sheet1!$A$2:$A$13)-ROW(Sheet1!$A$2)+1)/(Sheet1!$D$2:$D$13=$A2)/(Sheet1!$A$2:$A$13=$B2),1)),"")