advanced lookup/index-match problem

[tx12345]

H

Let's say I have this set of dat
_ a_b_
1 cl_o_5/15/0
2 cl_c_4/21/0
3 dj_o_6/19/0
4 dj_c_8/2/0
5 bp_o_3/21/0
6 oj_o_7/7/0
7 bo_c_7/15/0
8 cl_o_3/1/0

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 fin
out, using the list just the way it is, what the oldest date is for al
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
says "O", and make note of the date of each trade. Then, having gathere
the list of all relevant dates, tell me what the oldest date of th
bunch is. Thanks

Looking at the cl trades, the oldest open trade is 3/1/0

Any ideas are welcome

Many thank

t

 

[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]

Biff,

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

Thanks!

tx

 

[Biff]

You're welcome!

Biff

Biff,

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

Thanks!

tx