Date Before A Date With Matching Criteria

[martinpgibson]

Hi there,

I have the attached spreadsheet and I need to find the date in the table that is less than the specified date in E3 and is also the animal specified in cell D3. So the formula should bring back the result 18/03/2013 (line 4) as that is the closest date before the given date and it is also a dog.

I know I probably need an array formula and some max ifs, I have tried a few combos but I can't get it going. I don't want to be able to do any sorting of the table I kind of want to keep that how it is.

Your help is much much appreciated!

DATE BEFORE DATE.xlsx

[benishiryo]

hi martin. try:
=SUMPRODUCT(MAX((Table1[ANIMALS]=D3)*(Table1[DATES]<E3)*Table1[DATES]))

[martinpgibson]

Thank you so much, that worked perfectly!