Vlookup for a minimum value

[Hee Chee Soon]

Dear All,

I really face a problem here, can somebody help me?

A B C
Member's Code Introducer Name Date Join
123456 Jessica 1/4/2009
123457 May 3/5/2009
123458 Albert 3/5/2009
123459 Jessica 4/5/2009
123461 Steven 3/5/2009
123451 Jessica 5/4/2009
123586 May 6/5/2009
.... ... ...

At another worksheet, I need to use vlookup function to search any
introducer to find which is their 1st member with smallest date join without
going back to do sorting at my database. (What i need is their Date join
only... )

Do you have any idea to solve this problem?

Thank you very much.!!!!

Regards,
Hee Chee Soon

 

[Pete_UK]

Suppose you have the introducer's name in A1 of Sheet2 (other data in
Sheet1), then put this array* formula in B1 of Sheet2:

=MIN(IF((Sheet1!B$2:B$100=A1)*(Sheet1!C$2:C$100<>""),Sheet1!C$2:C
$100))

Format the cell as a date.

* An array formula must be committed using the key combination of Ctrl-
Shift-Enter (CSE) rather than the usual Enter. If you do this
correctly then Excel will wrap the formula with curly braces { } when
viewed in the formula bar - do not type these yourself. If you amend
the formula then you will need to use CSE again.

Hope this helps.

Pete

 

[Hee Chee Soon]

It works!!

Thanks Pete, really appreciate your helping hand.

Regards,
Hee Chee Soon

 

[Pete_UK]

You're welcome. Glad to hear it works for you - thanks for feeding
back.

Pete