Formula to Lookup Min value
 

Hi,

I have some data in the following format:

Ref Date
001 01/05/08
002a 10/05/08
002b 08/05/08
002c 12/05/08

I'd like a formula to use in another table that looks up anything with a ref
starting 002 and returns the earliest date (08/05/08).

Any help gratefully appreciated.

Thanks,
Gavin.

Formula to Lookup Min value
 

=MIN(IF((LEFT(A2:a5,3)="002"),B2:B5))
It is an array formula. Enter Ctrl+shift+enter
Change the range for your requirement.
best wishes
Sreedhar

Best wishes


"Gavin" wrote:

Hi,

I have some data in the following format:

Ref Date
001 01/05/08
002a 10/05/08
002b 08/05/08
002c 12/05/08

I'd like a formula to use in another table that looks up anything with a ref
starting 002 and returns the earliest date (08/05/08).

Any help gratefully appreciated.

Thanks,
Gavin.

Formula to Lookup Min value
 

=MIN(IF((LEFT(A2:A10,3)="002")*(B2:B10),(B2:B10)))

An array so enter with Ctrl+Shift+enter

"Gavin" wrote:

Hi,

I have some data in the following format:

Ref Date
001 01/05/08
002a 10/05/08
002b 08/05/08
002c 12/05/08

I'd like a formula to use in another table that looks up anything with a ref
starting 002 and returns the earliest date (08/05/08).

Any help gratefully appreciated.

Thanks,
Gavin.

Formula to Lookup Min value
 

Thanks Sreedhar.

Had to play around with it a bit, but all working now.

Cheers,
Gavin.

"yshridhar" wrote:

=MIN(IF((LEFT(A2:a5,3)="002"),B2:B5))
It is an array formula. Enter Ctrl+shift+enter
Change the range for your requirement.
best wishes
Sreedhar

Best wishes


"Gavin" wrote:

Hi,

I have some data in the following format:

Ref Date
001 01/05/08
002a 10/05/08
002b 08/05/08
002c 12/05/08

I'd like a formula to use in another table that looks up anything with a ref
starting 002 and returns the earliest date (08/05/08).

Any help gratefully appreciated.

Thanks,
Gavin.