INDEX SMALL multiple criteria, multiple results - slow?

[mcchieftan]

Hi guys,

I'm working on private accounting sheet and using this formula (sheet Aug15) to return currency values based on category and on transaction date falling between two dates from a data sheet (sheet data)

=IFERROR((INDEX(data!$A$1:$E$5000,SMALL(IF((data!$D$1:$D$5000=H$34)+(data!$A$1:$A$5000>=$C$1)+(data!$A$1:$A$5000<(EDATE($C$1,1)))=3,ROW(data!$A$1:$A$5000)),ROWS($1:1)),3)),"")

Since I've started using this formula I'm having increased processing time when manipulating data within the target range (sheet data)

For now I've reduced the range from 5000-1000 but this is only a short-term solution. Ideally I want to replicate the sheet Aug15 for other months (Jun15, Jul15 etc) as I have set the formulae up to perform the lookups depending upon the month indicated in the header. Can anyone advise on a) if this really is the source of the slowdown and b) if there is a more efficient way of doing this lookup?

Thanks!

 

[XOR LX]

Hi.

It doesn't seem that bad to me at the moment.

There are a few IF(ISERROR constructions in there which I'm a little confused about, since the majority appear to use the more efficient IFERROR.

However, even IFERROR can be very expensive. It's generally preferable to use a single cell which counts the expected number of returns and reference that in the formulas instead. I give an explanation as to why this is the case here:

http://superuser.com/questions/8127...l-multiple-corresponding-values/812848#812848

So, for example, for your formulas in the Aug15 sheet, beginning in cell B36, you could use the cell above, B35, to enter:

=COUNTIFS(data!$D$1:$D$5000,B$34,data!$A$1:$A$5000,">="&$C$1,data!$A$1:$A$5000,"<"&EDATE($C$1,1))

after which the array formula in B36 becomes:

=IF(ROWS($1:1)>B$35,"",INDEX(data!$A$1:$E$5000,SMALL(IF((data!$D$1:$D$5000=B$34)+(data!$A$1:$A$5000>=$C$1)+(data!$A$1:$A$5000<(EDATE($C$1,1)))=3,ROW(data!$A$1:$A$5000)),ROWS($1:1)),3))

and, to quote myself:

"...in rows beyond the expected number of returns, the IF clause returns TRUE and so a blank is returned. And the nice part about the IF function is that, if the clause passed to it is TRUE, then the FALSE part - here a large, resource-heavy array formula - does not even get considered for calculation.

This is not at all the case with the IFERROR version, which carries on churning away, oblivious to the fact that its calculations are needless and a burden on resource."

Also, I can't confirm this, though I have a suspicion that addition of Booleans in this way might be slightly more costly than repeated IF clauses, especially since you are then forcing an extra conditional check (i.e.=3). Hence I would prefer:

=IF(ROWS($1:1)>B$35,"",INDEX(data!$A$1:$E$5000,SMALL(IF(data!$D$1:$D$5000=B$34,IF(data!$A$1:$A$5000>=$C$1,IF(data!$A$1:$A$5000<EDATE($C$1,1),ROW(data!$A$1:$A$5000)))),ROWS($1:1)),3))

Regards

 

[mcchieftan]

My most sincere apologies for my tardy thanks! I'm running a mushroom farm startup and sometimes get distracted from the PC. Thanks for you help

 

[XOR LX]

You're welcome!