MATCH using multiple criteria?
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 work***), 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
.
