How can you include a maximum date criteria into a =FILTER function with other filter criteria?

[gck67]

Hello,

I would like to use the =FILTER function to return a dynamic array using two criteria: (1) A user specified "request_id" and (2) The maximum value of the "download_date_time" attribute for the records with the user specified request_id. In other words, extract the latest record for the specified request_id. Only one record should be returned.

It seems like a pretty straight forward use case, but I have had no success googeling for an answer to this. I must be over-looking something very obvious )

Any help would be very much appreciated.

Thank you,
Greg

example_latest_approval_stage.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
4
5								request_id	100
6	download_date_time	request_id	status	assigned_to	approval_unit
7	01.Jan.2023 10:10	100	open	jon	mgmt_1			01.Jan.2023 10:10	100	open	jon	mgmt_1
8	02.Jan.2023 10:10	110	open	mary	mgmt_2			07.Jan.2023 10:10	100	recruiting	jon	mgmt_1
9	03.Jan.2023 10:10	120	open	bill	mgmt_3
10	04.Jan.2023 10:10	130	open	jean	mgmt_1
11	05.Jan.2023 10:10	140	open	bob	mgmt_2
12	06.Jan.2023 10:10	150	open	ann	mgmt_3
13	07.Jan.2023 10:10	100	recruiting	jon	mgmt_1
14	08.Jan.2023 10:10	110	closed	mary	mgmt_2
15	09.Jan.2023 10:10	120	filled	bill	mgmt_3
16	10.Jan.2023 10:10	130	cancelled	jean	mgmt_1
17	11.Jan.2023 10:10	140	open	bob	mgmt_2
18	12.Jan.2023 10:10	150	open	ann	mgmt_3
19
Sheet1

Cell Formulas
Range	Formula
H7:L8	H7	=FILTER(tbl_status, tbl_status[request_id]=I5,)
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
If your dates are always sorted ascending then how about

=TAKE(FILTER(tbl_status, tbl_status[request_id]=I5,),-1)

 

[gck67]

Hi & welcome to MrExcel.
If your dates are always sorted ascending then how about

=TAKE(FILTER(tbl_status, tbl_status[request_id]=I5,),-1)

Hello Fluff,

Yes! That works perfectly. Thank you so much!

Thank you also for the welcome to MrExcel.

I won't mention how many hours I spent this morning googeling and looking through YT videos - and to get this answer in just a few minutes here in this forum. Wow, amazing - thank you, again.

Best regards,
Greg

 

[Fluff]

You're welcome & thanks for the feedback.