I have the formula below that is currently working to return the most recent date in a table based on the if criteria within the formula:
=MAX(IF(Table1[Vendor Description]=[@Vendor],Table1[Record Create Date]))
But what I need to now factor is in s a secondary criteria to return the same result:
If(table1[Enrollment Status Description]="Explicit Enrollment")
What I cannot figure out is how to get the two criteria to work together to return the desired result.
=MAX(IF(Table1[Vendor Description]=[@Vendor],Table1[Record Create Date]))
But what I need to now factor is in s a secondary criteria to return the same result:
If(table1[Enrollment Status Description]="Explicit Enrollment")
What I cannot figure out is how to get the two criteria to work together to return the desired result.
Explicit Enrollment File.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Vendor Code | Vendor Description | Producer Number | Enrollment Effective Date | Enrollment Termination Date | Enrollment Transaction Effective Date | Enrollment Status Code | Enrollment Status Description | ||
2 | 001 | Vendor 1 | A1 | 5/1/2009 | 5/1/2009 | EE | Explicit Enrollment | |||
3 | 001 | Vendor 1 | A2 | 3/4/2014 | 3/4/2014 | EE | Explicit Enrollment | |||
4 | 002 | Vendor 2 | A3 | 10/13/2008 | 10/13/2008 | EE | Derived Enrollment | |||
5 | 002 | Vendor 2 | A4 | 5/1/2009 | 5/1/2009 | EE | Explicit Enrollment | |||
6 | 003 | Vendor 3 | A5 | 5/1/2009 | 5/1/2009 | EE | Derived Enrollment | |||
7 | 003 | Vendor 3 | A6 | 2/18/2014 | 2/18/2014 | EE | Explicit Enrollment | |||
Sheet1 |