How to Use two search criteria in one array.

  • Thread starter Thread starter bob
  • Start date Start date
B

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
 
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
Click to expand...

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.
 
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 -
Click to expand...

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?
 
Back
Top