Return earliest date

[DebbieV]

Hi

I have the following data on 2 sheets in my workbook. In sheet 1 I
want it to check to see if any of the data appears in Sheet 2 and if
so, return the earliest date for that name. eg look for Bob in Sheet
2 and recognise that the earliest date is 31/5.


Sheet 1
A
1 Bob
2 Gary
3 Rick

Sheet 2
A B
1 Bob 2/06/2008
2 Gary 5/06/2008
3 Rick 1/06/2008
4 Rick 5/06/2008
5 Bob 31/05/2008
6 Bob 17/06/2008
7 Rick 2/06/2008

 

[muddan madhu]

try this
=SMALL(sheet2!B1:B6,MATCH(A1,sheet2!A1:A6,0))

 

[Bob Phillips]

=MIN(IF(Sheet2!$A$1:$A$7=A1,Sheet2!$B$1:$B$7))

this is an array formula, so commit with ctrl-shift-enter

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[DebbieV]

=MIN(IF(Sheet2!$A$1:$A$7=A1,Sheet2!$B$1:$B$7))

this is an array formula, so commit with ctrl-shift-enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)









- Show quoted text -

Thanks for the speedy replies! Worked great.