# Oldest Date

#### antrawson

##### New Member
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 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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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/.

Or

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

Or

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

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.

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 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 ...

Last edited:
Perhaps:

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

formated as a date.

Replies
11
Views
236
Replies
1
Views
155
Replies
13
Views
461
Replies
10
Views
487
Replies
5
Views
100

1,196,248
Messages
6,014,223
Members
441,808
Latest member
xplainer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back