How to Use two search criteria in one array.


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
 
Ad

Advertisements

J

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.
 
Ad

Advertisements

B

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top