Array formulas - too slow

[Guest]

Bob / Tom,

Thanks for your help.

I have used Bob's array formula to the problem I had:-

=INDEX(Sheet1!$J$1:$J$6,MATCH(1,(Sheet1!$A$1:$A$6=A8)*(MAX(IF((Sheet1!$A$1:$A$6=A8)*(Sheet1!$H$1:$H$6<>39052),Sheet1!$H$1:$H$6))=Sheet1!$H$1:$H$6),0))

I have to do some separate workings for this to work as I wanted it to.

For a lot of the MAX values it was looking up I was getting a zero value
which was correct. But if it returned a zero value I wanted it to look up the
preceeding value for the previous MAX value.

In any case:-

Is there any way around the above formula without using an array formula as
this creates massive downtime in my workbook. Is this a setup problem or a
common problem with array formulas?


Due to complexity of the formula etc and the array is this something that
can be done in VB?

Regards
Matthew Balch

 

[Tom Ogilvy]

Yes, an array formula can be can be slow to recalculate

Yes, you should be able to do it with macros. It depends on what you
situation is whether that is practical or not.

It is a one time thing or will the values being looked up or the values
being returned change frequently. Or is it a batch thing, done
periodically.

Apparently Bob understands what you want, so I will defer to him.