SOLVED

# Optimise XLOOKUP Formula for quicker updates

Copper Contributor

# 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.

2 Replies
best response confirmed by Cameron_Wilson (Copper Contributor)
Solution

# Re: Optimise XLOOKUP Formula for quicker updates

@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.

# Re: Optimise XLOOKUP Formula for quicker updates

@Riny_van_Eekelen Thankyou that has solved my problem, I can't believe it was so simple

1 best response

Accepted Solutions
best response confirmed by Cameron_Wilson (Copper Contributor)
Solution

# Re: Optimise XLOOKUP Formula for quicker updates

@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.