advanced lookup/index-match problem

Hi!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=MIN(IF((A1:A8="cl")*(B1:B8="o"),C1:C8))

Format as DATE

Better to use cells to hold the criteria:

E1 = cl
F1 = o

=MIN(IF((A1:A8=E1)*(B1:B8=F1),C1:C8))

Biff

"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
>
> Let's say I have this set of data
> _ a_b_c
> 1 cl_o_5/15/06
> 2 cl_c_4/21/06
> 3 dj_o_6/19/06
> 4 dj_c_8/2/06
> 5 bp_o_3/21/06
> 6 oj_o_7/7/06
> 7 bo_c_7/15/06
> 8 cl_o_3/1/06
>
> OK, so we have three columns of data: symbol, status (open or closed),
> and a date.
>
> Now, without having to do any special sorting, is there a way to find
> out, using the list just the way it is, what the oldest date is for all
> open cl trades, and then all bp trades, etc?
>
> How can I tell excel to:
>
> "take a look inside of column A for all instances of cl, where column B
> says "O", and make note of the date of each trade. Then, having gathered
> the list of all relevant dates, tell me what the oldest date of the
> bunch is. Thanks"
>
> Looking at the cl trades, the oldest open trade is 3/1/06
>
> Any ideas are welcome.
>
> Many thanks
>
> tx
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile:
> http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=570616
>

Biff,

right, an array. I'll give it a try.

Thanks!

tx

Originally Posted by Biff

Hi!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=MIN(IF((A1:A8="cl")*(B1:B8="o"),C1:C8))

Format as DATE

Better to use cells to hold the criteria:

E1 = cl
F1 = o

=MIN(IF((A1:A8=E1)*(B1:B8=F1),C1:C8))

Biff

"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
>
> Let's say I have this set of data
> _ a_b_c
> 1 cl_o_5/15/06
> 2 cl_c_4/21/06
> 3 dj_o_6/19/06
> 4 dj_c_8/2/06
> 5 bp_o_3/21/06
> 6 oj_o_7/7/06
> 7 bo_c_7/15/06
> 8 cl_o_3/1/06
>
> OK, so we have three columns of data: symbol, status (open or closed),
> and a date.
>
> Now, without having to do any special sorting, is there a way to find
> out, using the list just the way it is, what the oldest date is for all
> open cl trades, and then all bp trades, etc?
>
> How can I tell excel to:
>
> "take a look inside of column A for all instances of cl, where column B
> says "O", and make note of the date of each trade. Then, having gathered
> the list of all relevant dates, tell me what the oldest date of the
> bunch is. Thanks"
>
> Looking at the cl trades, the oldest open trade is 3/1/06
>
> Any ideas are welcome.
>
> Many thanks
>
> tx
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile:
> http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=570616
>

You're welcome!

Biff

"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> Biff,
>
> right, an array. I'll give it a try.
>
> Thanks!
>
> tx
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
>>
>> =MIN(IF((A1:A8="cl")*(B1:B8="o"),C1:C8))
>>
>> Format as DATE
>>
>> Better to use cells to hold the criteria:
>>
>> E1 = cl
>> F1 = o
>>
>> =MIN(IF((A1:A8=E1)*(B1:B8=F1),C1:C8))
>>
>> Biff
>>
>> "tx12345" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > Hi
>> >
>> > Let's say I have this set of data
>> > _ a_b_c
>> > 1 cl_o_5/15/06
>> > 2 cl_c_4/21/06
>> > 3 dj_o_6/19/06
>> > 4 dj_c_8/2/06
>> > 5 bp_o_3/21/06
>> > 6 oj_o_7/7/06
>> > 7 bo_c_7/15/06
>> > 8 cl_o_3/1/06
>> >
>> > OK, so we have three columns of data: symbol, status (open or
>> closed),
>> > and a date.
>> >
>> > Now, without having to do any special sorting, is there a way to
>> find
>> > out, using the list just the way it is, what the oldest date is for
>> all
>> > open cl trades, and then all bp trades, etc?
>> >
>> > How can I tell excel to:
>> >
>> > "take a look inside of column A for all instances of cl, where column
>> B
>> > says "O", and make note of the date of each trade. Then, having
>> gathered
>> > the list of all relevant dates, tell me what the oldest date of the
>> > bunch is. Thanks"
>> >
>> > Looking at the cl trades, the oldest open trade is 3/1/06
>> >
>> > Any ideas are welcome.
>> >
>> > Many thanks
>> >
>> > tx
>> >
>> >
>> > --
>> > tx12345
>> >
>> ------------------------------------------------------------------------
>> > tx12345's Profile:
>> > http://www.excelforum.com/member.php...o&userid=24776
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=570616
>> >
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile:
> http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=570616
>