Lookup MIN Date

[dcr]

I have inherited a spreadsheet with a formula that does not work. Instead of
returning the earliest order date it returns the first order date. I would
like a formula to place in Sheet 2 that would populate the earliest order
date.

Sheet 1

Part Number Order Date
111 9/10/2007
113 1/1/2007
111 1/1/2007
113 2/1/2002

Sheet 2

Part Number Earliest Order Date
111 1/1/2007
113 2/1/2002

Thank you in advance!

 

[Pete_UK]

Try this array* formula in B2 of Sheet2:

=MIN(IF((Sheet1!A2:A100=A2),Sheet1!B2:B100,10E10))

* Array formulae need to be committed using the key combiation Ctrl-
Shift-Enter (CSE) instead of the usual <enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yoruself. Use CSE
again if you subsequently edit or amend the formula.

Hope this helps.

Pete

 

[Bob Phillips]

=MIN(IF(Sheet1!$A$2:$A$200=$A2,Sheet1!$B$2:$B$200))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

 

[dcr]

Bob,

Thank you for the reply. The result was the earliest order date for the
entire Sheet 1, not just for part number 111. Any suggestions? Is a VLOOKUP
required?

 

[Peo Sjoblom]

Did you read what Bob said?


"this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter"

--


Regards,


Peo Sjoblom

 

[dcr]

I used Enter instead of CTRL+Shift+Enter. Now it is working!!

Thank you

 

[Bob Phillips]

=MIN(IF(Sheet1!A2:A100=A2,Sheet1!B2:B100))

which is an array formula, so commit with Ctrl-Shift-enter not just Enter