return closest date to today with criteria

[riiiiiichhhhhhhhh]

hi guys,

can help with a formula to return a date according to criteria on column E?

id	status	date	id no duplicates
1010	paid	08/10/2021	1010	return the date which is closest to and sooner than today for id no duplicates 1010 where status is "paid" from columns A to C
1010	paid	01/10/2022	1012	return the date which is closest to and sooner than today for id no duplicates 1012 where status is "paid" from columns A to C
1012	to be paid	05/10/2022
1012	paid	22/02/2022
1012	paid	01/01/2021

 

[Z51]

Try this...

=MAXIFS(C:C,A:A,D2,B:B,"paid",C:C,"<"&TODAY())

 

[riiiiiichhhhhhhhh]

thanks,
it seems that this formula does not work on excel 2016

 

[Peter_SSs]

What are your expected results for that sample data and why?

 

[riiiiiichhhhhhhhh]

What are your expected results for that sample data and why?

hi peter, my profile suggests i have the 2016 version, am i missing smt?

 

[Peter_SSs]

am i missing smt?

Yes, you are missing the expected results in your sample data and the explanation of why they are the expected results.

What are your expected results for that sample data and why?