Oldest Date

[antrawson]

Morning all,
I have used an array formula previously to search for oldest date however cant adapt this to work on what I have on another sheet.

Basically I have two columns lets say A and B

In column A I have loads of project names, there will be repeats here. In column B I have dates.

eg


COLUMN A ............COLUMN B
Making toast ............01/01/13
Making toast ............02/01/13
Making bread ..............01/21/13
Making eggs ............03/01/13
Making eggs ..............08/03/13
Making toast............. 01/01/13
Making toast ..............01/02/13
Making toast .............09/03/13
Making toast ...............01/01/13

basically i want a thing where it looks up "making toast" and tells me the earliest date in column b

thanks

 

[Firefly2012]

Hi

Try:

=MIN(IF(A2:A10="Making Toast",B2:B10))

entered with Ctrl+Shift+Enter. Make sure you format this formula cell with a date format.

The hardcoded "Making Toast" above can be a cell reference containing the required value/.

 

[Sixthsense]

Or

=LOOKUP("Making Toast",A:A,B:B)

Or

=VLOOKUP("Making Toast",A:B,2,1)

 

[Jonmo1]

I think you want vlookup with exact match to ensure it returns the FIRST matching value from column A.
=VLOOKUP("Making Toast",A:B,2,0)

The "closest match" lookups might not return the first match (therefore not the earliest date in B)


This is assuming the dates in column B are sorted ascending.

If Col B is NOT sorted, then you need Firefly's Min(If solution.

 

[antrawson]

ah smashing, the "min" one worked. Heres another one (apologies!)

What if the date is missing in some of the rows, I want it to pick the date in the future eg target completion date if that makes sense. In theory there should only be one target completion date

COLUMN A ............COLUMN g
Making toast ............
Making toast ............
Making bread ..............01/11/13
Making eggs ............
Making eggs ..............08/03/13
Making toast.............
Making toast ..............
Making toast .............
Making toast ...............01/10/13

i tried =VLOOKUP(D3,Data!A:G,7,0) but it doesnt seem to do what i want really ...

 

[Firefly2012]

Perhaps:

=LOOKUP(2,1/((A2:A10="Making toast")*ISNUMBER(G2:G10)),G2:G10)

formated as a date.