Forum Discussion

Cameron_Wilson's avatar
Cameron_Wilson
Copper Contributor
Aug 23, 2024

Optimise XLOOKUP Formula for quicker updates

Hello all, I have been revamping a spreadsheet recently and have worked up a solution using an XLOOKUP formula however the Calculation response is about 30s. I believe it would work better on a stronger computer however that's not likely to change so thus I need to look at the formula.

Background - Formula was previously a VLOOKUP searching within date ranges but would find the earliest entry and carry it over instead which lead to data not showing in particular date ranges a

 

 

 

=XLOOKUP(1,(Baled!A:A='Isis Report Conversion'!A3)*(Baled!I:I>='Isis Report Conversion'!$B$1)*(Baled!I:I<='Isis Report Conversion'!$D$1),Baled!I:I)

 

 

 


I have the formula checking for entries on a separate sheet within a certain date range set by 

(see highlighted below)
=XLOOKUP(1,(Baled!A:A='Isis Report Conversion'!A3)*(Baled!I:I>='Isis Report Conversion'!$B$1)*(Baled!I:I<='Isis Report Conversion'!$D$1),Baled!I:I) 

I have this formula or specific variations of it looking for different values under 4 Headers for a total of 134 Rows - Am I asking to much of the hardware I have access to or is this formula able to be optimised? Happy to try any variations or idea's as this sheet is not currently in production.


Excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Aug 23, 2024

    Cameron_Wilson Do not use entire column references like A:A and I:I. Tried a similar formula on my PC and could see Excel was updating cells with some delay as it is creating arrays with 1048576 elements for every check in the formula.

    Changed the references to A1:A1000 and I1:I1000 and couldn't notice any calculation delays. See if that works for you. Just use a range that is large enough to cover the actual data range in your file.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Aug 23, 2024

    Cameron_Wilson Do not use entire column references like A:A and I:I. Tried a similar formula on my PC and could see Excel was updating cells with some delay as it is creating arrays with 1048576 elements for every check in the formula.

    Changed the references to A1:A1000 and I1:I1000 and couldn't notice any calculation delays. See if that works for you. Just use a range that is large enough to cover the actual data range in your file.

Resources