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.
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. Read More
