How to Use two search criteria in one array.

[bob]

Hi friends, having had no success with previous post, this one may be
more understandable.(INDEX,MATCH) array requiring two Match's in same
index.

A B C D
(cell="F")
1 Symbol Date Price
2 F 01/15/10 6.28
3 LSI 02/05/10 5.00
4 F 05/02/10 15.00

{=INDEX(A2:C4,MATCH(D1,A2:A4,0),3)}

The above formula works fine except it returns "6.28", the first date
and Price for Symbol "F".

It needs to be extended to include a similar match for the LAST Date,
Column(B) that "F" appears in, i.e. 15.00 Column C(3).

Any Help ASAP is Greatly Appreciated.
Bob

 

[James Ravenswood]

Hi friends, having had no success with previous post, this one may be
more understandable.(INDEX,MATCH) array requiring two Match's in same
index.

        A             B                     C              D
(cell="F")
1      Symbol   Date               Price
2     F             01/15/10       6.28
3     LSI         02/05/10      5.00
4     F             05/02/10    15.00

{=INDEX(A2:C4,MATCH(D1,A2:A4,0),3)}

The above formula works fine except it returns "6.28", the first date
and Price for Symbol "F".

It needs to be extended to include a similar match for the LAST Date,
Column(B) that "F" appears in, i.e. 15.00 Column C(3).

Any Help ASAP is Greatly Appreciated.
Bob

Here is a "semi-solution":

=SUMPRODUCT((A2:A4="F")*(B2:B4=MAX(B2:B4))*(C2:C4))

The reason its only "semi" is that it finds the row with "F" and the
latest date and not the last date. Good enough if the dates are
sorted.

 

[bob]

Here is a "semi-solution":

=SUMPRODUCT((A2:A4="F")*(B2:B4=MAX(B2:B4))*(C2:C4))

The reason its only "semi" is that it finds the row with "F" and the
latest date and not the last date.  Good enough if the dates are
sorted.- Hide quoted text -

- Show quoted text -

I thank Mr. Ravenswood for his efforts, BUT, it appears his formula
returns a result based on a selection matching the largest date,
rather than largest date matching my selection, and then returning
associated same row data.

Can anyone please help with this problem?