max order date that has 2 same dates and get the lowest value

[hua1232313]

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.

[AliGW]

Welcome to the forum.

You need to build a MIN statement into your formula.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Glenn Kennedy]

One way, using this array formula:

=MIN(IF($C$2:$C$100=MAX($C$2:$C$100),$D$2:$D$100))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Next time please attach a sample sheet, as the forum messes up formatting. It's hard to see what's meant to be in which cell. Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Glenn Kennedy]

Non-array version (just use enter):

=AGGREGATE(15,6,$D$2:$D$7/($C$2:$C$7=AGGREGATE(14,6,$C$2:$C$7,1)),1)

[hua1232313]

Thank you AliGW and Glenn Kennedy for the response and help. It took me awhile to respond since I just came back to work.

Was about to post the sample excel sheet. Will try first the solution and was provided and will get back.