Pulling the latest data for a specific data

[RMJ]

Hello,

I'm working with 2 spreadsheets and trying to pull the latest entry for a specific data group. I can successfully pull the last row of data from the 1st spreadsheet into the 2nd spreadsheet using the LOOKUP formula; but having an issue with pulling the first 2 lines of data that are from the latest hour. Please see example below:
I'm unable to pull data from rows 5 & 6. I have tried Vlookup, Index & Match, Offset and a couple of more scenarios; but don't get the data to transfer over
On line 7, I use the LOOKUP formula that looks for the last data entry for the day.; but not having luck figuring out how to bring the data over from lines 5 &6 (or reference the other 2 workgroups in the scenario

Please let me know if you have any questions.

Thank you,
RMJ

Date	Name	Time AZ	EST Time	Pen:	Int	# of Accounts	Calls Connected	Agents logged in	Aban OB Count/Rate	Offered Calls	Aban IB Count/Rate
03/30/21	Corres	9:54	12:54:00 PM	94%	172%	500	40(5.01%)	14	4(10%)	7	0(0.00%)
03/30/21	Corres X2	9:54	12:54:00 PM	99%	146%	466	25(7.74%)	14	2(8.0%)	N/A	N/A
03/30/21	Corres x7	9:54	12:54:00 PM	97%	190%	342	32(4.22%)	14	4 (4.12%)	N/A	N/A
03/30/21	Corres	12:00	3:00:00 PM	95%	211%	500	46(4.68%)	18	5(10.86%)	14	2(16.31%)
03/30/21	Corres X2	12:00	3:00:00 PM	99%	148%	466	25(7.65%)	18	2 (8%)	N/A	N/A
03/30/21	Corres x7	12:00	3:00:00 PM	98%	222%	342	39(4.39%)	18	4 (10.25%)	N/A	N/A

 

[Toadstool]

Hi RMJ,

As long as the data is in time within date sequence then this would work:

RMJ2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Name	Time AZ	EST Time	Pen:	Int	# of Accounts	Calls Connected	Agents logged in	Aban OB Count/Rate	Offered Calls	Aban IB Count/Rate		Date	Name on Last 3
2	3/30/2021	Corres	9:54	12:54:00 PM	94%	172%	500	40(5.01%)	14	4(10%)	7	0(0.00%)		3/30/2021	Corres
3	3/30/2021	Corres X2	9:54	12:54:00 PM	99%	146%	466	25(7.74%)	14	2(8.0%)	N/A	N/A			Corres X2
4	3/30/2021	Corres x7	9:54	12:54:00 PM	97%	190%	342	32(4.22%)	14	4 (4.12%)	N/A	N/A			Corres x7
5	3/30/2021	Corres	12:00	3:00:00 PM	95%	211%	500	46(4.68%)	18	5(10.86%)	14	2(16.31%)
6	3/30/2021	Corres X2	12:00	3:00:00 PM	99%	148%	466	25(7.65%)	18	2 (8%)	N/A	N/A
7	3/30/2021	Corres x7	12:00	3:00:00 PM	98%	222%	342	39(4.39%)	18	4 (10.25%)	N/A	N/A
8
Sheet1

Cell Formulas
Range	Formula
O2:O4	O2	=INDEX($B$2:$B$9999,AGGREGATE(14,6,ROW($A$2:$A$9999)-ROW($A$1)/($A$2:$A$9999=$N$2),ROW($O$1)+4-ROW()))

 

[RMJ]

Not sure what I'm doing wrong; but I get a #NUM message, instead of having the data transfer from the 1st spreadsheet to the 2nd spreadsheet.

 

[Toadstool]

It will give #NUM if no match is found. Here, I've put it on a new sheet but make sure you name them Sheet1 and Sheet2

RMJ2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Name	Time AZ	EST Time	Pen:	Int	# of Accounts	Calls Connected	Agents logged in	Aban OB Count/Rate	Offered Calls	Aban IB Count/Rate
2	3/30/2021	Corres	9:54	12:54:00 PM	94%	172%	500	40(5.01%)	14	4(10%)	7	0(0.00%)
3	3/30/2021	Corres X2	9:54	12:54:00 PM	99%	146%	466	25(7.74%)	14	2(8.0%)	N/A	N/A
4	3/30/2021	Corres x7	9:54	12:54:00 PM	97%	190%	342	32(4.22%)	14	4 (4.12%)	N/A	N/A
5	3/30/2021	Corres	12:00	3:00:00 PM	95%	211%	500	46(4.68%)	18	5(10.86%)	14	2(16.31%)
6	3/30/2021	Corres X2	12:00	3:00:00 PM	99%	148%	466	25(7.65%)	18	2 (8%)	N/A	N/A
7	3/30/2021	Corres x7	12:00	3:00:00 PM	98%	222%	342	39(4.39%)	18	4 (10.25%)	N/A	N/A
Sheet1

RMJ2.xlsx
	A	B
1	Date	Name on Last 3
2	3/30/2021	Corres
3		Corres X2
4		Corres x7
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(INDEX(Sheet1!$B$2:$B$9999,AGGREGATE(14,6,ROW(Sheet1!$A$2:$A$9999)-ROW(Sheet1!$A$1)/(Sheet1!$A$2:$A$9999=$A$2),ROW($B$1)+4-ROW())),"No match")