Excel formula nested functions max, if, and multiple criteria issues

**Jack7774** · 04-19-2016, 03:23 PM

The goal is to find the max date or greatest date ACP Principal Amount was seen before 12/31/2015 but not including 12/31/2015 and the answer is 11/28/2015. I tried the formula below but I've done something wrong with the multiple criteria part or the AND function possibly.

Please Login or Register  to view this content.

The attached spreadsheet shows in yellow where I would like the formula to go, in red what I tried, and orange what I would like the answer to be.

To attempt to explain the answer should look in column I for all cells that match cell Y2 or ACP Principal Amount, then out of the range of cells phone that match that criteria it would then look for the adjacent cells in column E for dates that are less than Y1 or 12/31/2015, lastly it would bring the max date out of those cells found.

I'd like the answer to use max, if, and, functions but if it can't or even if it can I always want to learn alternative methods to solve any problem as well. I love to learn. Thanks ahead of time. Rep will be given!