Lookup MIN Date

  • Thread starter Thread starter dcr
  • Start date Start date
D

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!
 
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
 
=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.
 
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?
 
Did you read what Bob said?


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

--


Regards,


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

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