Using MAX() in Excel against a list of dates using multiple criteria

I've searched and searched and trialled every answer but none seem to work in my situation.

Using Excel 2003.

I have a table with the columns [DATE], [SITE], [INCIDENT].

I'll explain in something similar to SQL because it's nice and clear. I need to run the following SELECT statement on the data in that table

SELECT [DATE] from Table1 WHERE [SITE] = "Site1" AND [INCIDENT] = "Death"

From that list (multiple values), I then need to display the latest [DATE] value ie. the last date a death occured.

I've tried this following formula:

{=MAX(IF('Spreadsheet1'!D3:D1000="Death", IF('Spreadsheet1'!E3:E1000="Site1", 'Spreadsheet1'!A3:A1000, 0), 0))}

But this does not seem to work correctly.

Any help is definitely welcome.

Answer

Try

=MAX(A3:A1000 * (D3:D1000="Death") * (E3:E1000="Site1"))

Rather than treating your WHERE clauses as conditions, this simply treats them as part of the expression that you are maximizing.  TRUE is 1 and FALSE is 0, so value × boolean₁ × boolean₂ is value if both Booleans are TRUE and 0 otherwise.

Naturally, it’s an array formula; but I see you already understand those.