MATCH using multiple criteria?

[Bob Tarburton]

Is there a way to find the first row in a data set that meets multiple
criteria?
Something that works like =MATCH(Criteria,Array,0) except for multiple
criteria in multiple columns.
I would also like to know if there is a way to find the LAST row that meets
these criteria.

I am using SUMPRODUCT to count and sum in a large database using predefined
ranges.
I am trying to to speed up calculation by limiting the defined ranges to
include only the applicable rows, instead of the entire database.
I'm using 5 conditions that the user defines with drop down boxes.
The conditions for the date range include >= and <= in the sumproduct
functions.

I can do this by using an extra column:
=IF(AND("condition1 is met","condition2 is met",etc),="cell above"+1,="cell
above")
copy down, then
=MATCH(1,"extra column",0) gives the first row
=MATCH(MAX("extra column"),"extra column",0) gives the last row

However, even though 60,000 of these IF statements (I have 10,000 rows and 6
user defined groups, time periods, etc in the report) only take 3 seconds to
calculate on their own (using calcualte worksheet), the entire report takes
about 20% longer to recalculate.
We can live with it for now, but we keep adding data.

If the answer is no, and this won't work to speed up calculation, that's
okay.
Please tell if that is because there is no better way to match on multiple
criteria, or because it will slow the calculation anyway..

Thanks for any help you can offer.
Bob

[daddylonglegs]

I'm not sure if it would actually speed things up for you but you could find the first match for 2 conditions something like this

=MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banana"),0),0)

and the last with

=MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0))

you can add more conditions quite easily

[Tom Ogilvy]

Looks like an array formula - don't think you can speed up an array formula
by using another array formula to make the first array formula do less work.

If you don't think sumproduct is an array formula, think again.

> you can add more conditions quite easily
And more processing time <g>

--
Regards.
Tom Ogilvy


"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> I'm not sure if it would actually speed things up for you but you could
> find the first match for 2 conditions something like this
>
> =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banana"),0),0)
>
> and the last with
>
> =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0))
>
> you can add more conditions quite easily
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=507249
>

[Bob Tarburton]

THANKS
That cut the calculation time for a specific report from 105 seconds to 11
seconds!!!!
I guess you're my daddy now

I had to add a set of parenthesis after the 1/ and before the next comma:
=MATCH(2,INDEX(1/((A1:A1000="apple")*(B1:B1000="banana")),0))
even though this should not make a difference (maybe because I'm using 5
criteria instead of 2?).

Without the extra parenthesis, I keep getting the last row of data instead
of the last that met the conditions.
The formula for the first row works without the extra parenthesis. Go
figure.


"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> I'm not sure if it would actually speed things up for you but you could
> find the first match for 2 conditions something like this
>
> =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banana"),0),0)
>
> and the last with
>
> =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0))
>
> you can add more conditions quite easily
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=507249
>

[Bob Tarburton]

It increases the speed by reducing the size of the arrays that each
sumproduct formula calculates.
I certainly get what you're saying, but I'm using about 250 sumproduct
formulas for each of 6 groups (1500 total) with 5 criteria each.
Adding 12 formulas to minimize the range for the sumproduct formulas makes
little difference in the worst case scenario, and a lot of difference in the
best.

I have 10,000+ rows of data (so far). If I want to compare 6 time periods,
the sumproduct formulas only have to look at about 1,7000 rows for each time
period, instead each one looking at 10,000 rows. Of course I have to keep
the data sorted in a logical way to best reduce the number of rows in each
group.

For example, the user could define the report to compare 6 salespeople over
the the entire time, in which case the speed is not increased unless the
data is sorted on salesperson.

However, there is usually a limited time period specified for each group, so
as long as the data is sorted on time period (quarter), region, and class of
customer, the speed for most desired reports will be improved.



"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Looks like an array formula - don't think you can speed up an array
> formula
> by using another array formula to make the first array formula do less
> work.
>
> If you don't think sumproduct is an array formula, think again.
>
>> you can add more conditions quite easily
> And more processing time <g>
>
> --
> Regards.
> Tom Ogilvy
>
>
> "daddylonglegs"
> <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> I'm not sure if it would actually speed things up for you but you could
>> find the first match for 2 conditions something like this
>>
>> =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banana"),0),0)
>>
>> and the last with
>>
>> =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0))
>>
>> you can add more conditions quite easily
>>
>>
>> --
>> daddylonglegs
>> ------------------------------------------------------------------------
>> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=507249
>>
>
>

[Tom Ogilvy]

Different visualizations of what you were asking. It appears you have set
aside a separate area to define your ranges once rather than embed such
logic in each sumproduct formula - which is what I understood you wanted to
do.

--
Regards,
Tom Ogilvy

"Bob Tarburton" <reply2bob@_removethis_intergate.com> wrote in message
news:[email protected]...
> It increases the speed by reducing the size of the arrays that each
> sumproduct formula calculates.
> I certainly get what you're saying, but I'm using about 250 sumproduct
> formulas for each of 6 groups (1500 total) with 5 criteria each.
> Adding 12 formulas to minimize the range for the sumproduct formulas makes
> little difference in the worst case scenario, and a lot of difference in
the
> best.
>
> I have 10,000+ rows of data (so far). If I want to compare 6 time periods,
> the sumproduct formulas only have to look at about 1,7000 rows for each
time
> period, instead each one looking at 10,000 rows. Of course I have to keep
> the data sorted in a logical way to best reduce the number of rows in each
> group.
>
> For example, the user could define the report to compare 6 salespeople
over
> the the entire time, in which case the speed is not increased unless the
> data is sorted on salesperson.
>
> However, there is usually a limited time period specified for each group,
so
> as long as the data is sorted on time period (quarter), region, and class
of
> customer, the speed for most desired reports will be improved.
>
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Looks like an array formula - don't think you can speed up an array
> > formula
> > by using another array formula to make the first array formula do less
> > work.
> >
> > If you don't think sumproduct is an array formula, think again.
> >
> >> you can add more conditions quite easily
> > And more processing time <g>
> >
> > --
> > Regards.
> > Tom Ogilvy
> >
> >
> > "daddylonglegs"
> > <[email protected]>
> > wrote in message
> > news:[email protected]...
> >>
> >> I'm not sure if it would actually speed things up for you but you could
> >> find the first match for 2 conditions something like this
> >>
> >> =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banana"),0),0)
> >>
> >> and the last with
> >>
> >> =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0))
> >>
> >> you can add more conditions quite easily
> >>
> >>
> >> --
> >> daddylonglegs
>
>> ------------------------------------------------------------------------
> >> daddylonglegs's Profile:
> > http://www.excelforum.com/member.php...o&userid=30486
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=507249
> >>
> >
> >
>
>

[Bob Tarburton]

That's right, and thanks for help on this and previous, you always help me
visualize what I'm doing right and/or wrong.

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Different visualizations of what you were asking. It appears you have set
> aside a separate area to define your ranges once rather than embed such
> logic in each sumproduct formula - which is what I understood you wanted
> to
> do.
>
> --
> Regards,
> Tom Ogilvy
>
> "Bob Tarburton" <reply2bob@_removethis_intergate.com> wrote in message
> news:[email protected]...
>> It increases the speed by reducing the size of the arrays that each
>> sumproduct formula calculates.
>> I certainly get what you're saying, but I'm using about 250 sumproduct
>> formulas for each of 6 groups (1500 total) with 5 criteria each.
>> Adding 12 formulas to minimize the range for the sumproduct formulas
>> makes
>> little difference in the worst case scenario, and a lot of difference in
> the
>> best.
>>
>> I have 10,000+ rows of data (so far). If I want to compare 6 time
>> periods,
>> the sumproduct formulas only have to look at about 1,7000 rows for each
> time
>> period, instead each one looking at 10,000 rows. Of course I have to keep
>> the data sorted in a logical way to best reduce the number of rows in
>> each
>> group.
>>
>> For example, the user could define the report to compare 6 salespeople
> over
>> the the entire time, in which case the speed is not increased unless the
>> data is sorted on salesperson.
>>
>> However, there is usually a limited time period specified for each group,
> so
>> as long as the data is sorted on time period (quarter), region, and class
> of
>> customer, the speed for most desired reports will be improved.
>>
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Looks like an array formula - don't think you can speed up an array
>> > formula
>> > by using another array formula to make the first array formula do less
>> > work.
>> >
>> > If you don't think sumproduct is an array formula, think again.
>> >
>> >> you can add more conditions quite easily
>> > And more processing time <g>
>> >
>> > --
>> > Regards.
>> > Tom Ogilvy
>> >
>> >
>> > "daddylonglegs"
>> > <[email protected]>
>> > wrote in message
>> > news:[email protected]...
>> >>
>> >> I'm not sure if it would actually speed things up for you but you
>> >> could
>> >> find the first match for 2 conditions something like this
>> >>
>> >> =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banana"),0),0)
>> >>
>> >> and the last with
>> >>
>> >> =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0))
>> >>
>> >> you can add more conditions quite easily
>> >>
>> >>
>> >> --
>> >> daddylonglegs
>>
>>> ------------------------------------------------------------------------
>> >> daddylonglegs's Profile:
>> > http://www.excelforum.com/member.php...o&userid=30486
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=507249
>> >>
>> >
>> >
>>
>>
>
>