Vlookup for a minimum value
 

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

Vlookup for a minimum value
 

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

On Aug 14, 9:09*am, Hee Chee Soon <Hee Chee
wrote:
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

Vlookup for a minimum value
 

It works!!

Thanks Pete, really appreciate your helping hand.

Regards,
Hee Chee Soon

"Pete_UK" wrote:

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

On Aug 14, 9:09 am, Hee Chee Soon <Hee Chee
wrote:
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

Vlookup for a minimum value
 

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

Pete

On Aug 14, 10:01*am, Hee Chee Soon
wrote:
It works!!

Thanks Pete, really appreciate your helping hand.

Regards,
Hee Chee Soon



"Pete_UK" wrote:
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

On Aug 14, 9:09 am, Hee Chee Soon <Hee Chee
wrote:
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- Hide quoted text -

- Show quoted text -