Is there a way to make this formula run much faster?

Question

I have a formula with quite a few moving parts, taking data from very large datasets. It is so extensive that it is damn near crashing my computer I do not have the option of adding helper columns or editing the source data, and I would prefer not have to manually select the used cells because they are constantly being added.

Is there a good way to make this run faster that I am simply missing?

Here is the formula: =IFERROR(TAKE(TAKE(SORT(FILTER('TELEDATA IN PROGRESS (9.22.23)'!E:T,('TELEDATA IN PROGRESS (9.22.23)'!G:G=BI2)*('TELEDATA IN PROGRESS (9.22.23)'!B:B="MOL ")*('TELEDATA IN PROGRESS (9.22.23)'!U:U>0)),16,-1,),1),,1),"")

Answer 1

You can try next suggestions.

First, you can replace 2 nested TAKE by INDEX to get the top left item (it's not the performance impact):

=IFERROR(
    INDEX(
        SORT(
            FILTER('TELEDATA IN PROGRESS (9.22.23)'!E:T,
                ('TELEDATA IN PROGRESS (9.22.23)'!G:G=BI2)*('TELEDATA IN PROGRESS (9.22.23)'!B:B="MOL ")*('TELEDATA IN PROGRESS (9.22.23)'!U:U>0)
            )
            ,16,-1,
        )
        ,1,1
    )
    ,""
)

Second, considering the data source name, I can suppose what you search the max number in the T column. In this case you can use XLOOKUP and MAXIFS functions instead of IFERROR, SORT and FILTER combination:

=XLOOKUP(
    MAXIFS(T:T
        ,'TELEDATA IN PROGRESS (9.22.23)'!G:G,"="&BI2
        ,'TELEDATA IN PROGRESS (9.22.23)'!B:B,"=MOL "
        ,'TELEDATA IN PROGRESS (9.22.23)'!U:U,">0"
    )
    ,'TELEDATA IN PROGRESS (9.22.23)'!T:T
    ,'TELEDATA IN PROGRESS (9.22.23)'!E:E
    ,""
)

FILTER and SORT create intermediate arrays and thus decrease performance. SORT performs in O(n*log(n)) in the best, while MAX or MIN and XLOOKUP (in the worst) in O(n) each, totally in O(2n).

Of cause, you need to limit the range (don't refer whole columns).