scenario: Get the max order date, if it has same date. Get the lowest value from another cell.
example:
column A column B column C column D
item1 red Jan 11, 2010 900
item2 white Jan 12, 2010 200
item3 green Jan 14, 2010 300
item4 blue Jan 13, 2010 600
item3 green Jan 14, 2010 50
item3 green Jan 14, 2010 250

output on should be: 50
since the last date is Jan 14, 2010 but the value should be 50 since the lowest value for same date.
=MAX(IF(A1=Sheet1!A1:A200,Sheet1!D1:D200,""))

It works for some cells but some are not. There are items that the output is correct but sometimes it would get the largest value.

Any ideas on how to modify the formula?

Thank you in advance.