# How to find the latest date based on criteria

Copper Contributor

# How to find the latest date based on criteria

Hi,

Can someone please help me in developing a formula/pivot that allows me to find the latest date based on some criteria. Below is the example Excel that is similar to what I am using:

 Fund Name Valuation Date Data Received Fund 1 31/12/21 Yes Fund 1 31/03/22 Yes Fund 1 30/06/22 Yes Fund 1 30/09/22 Yes Fund 1 31/12/22 No Fund 2 31/12/21 Yes Fund 2 31/03/22 Yes Fund 2 30/06/22 Yes Fund 2 30/09/22 No Fund 2 31/12/22 No

In the output I would expect the fund name and the latest date (where data received = yes) to be returned as per the below:

 Fund Name Valuation Date Fund 1 30/09/22 Fund 2 30/06/22

Any help would be greatly appreciated.

Thanks,

Danny

2 Replies

# Re: How to find the latest date based on criteria

``=LARGE(IF((\$A\$2:\$A\$11=E2)*(\$C\$2:\$C\$11="Yes"),\$B\$2:\$B\$11),1)``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

# Re: How to find the latest date based on criteria

Here is a PivotTable solution.