Return earliest date
 

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

Return earliest date
 

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

On Jun 4, 10:46*am, DebbieV wrote:
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

Return earliest date
 

=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" wrote in message
...
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

Return earliest date
 

On Jun 4, 5:55*pm, "Bob Phillips" wrote:
=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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Thanks for the speedy replies! Worked great.