Forum Discussion
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
=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.
