Forum Discussion
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.
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 Thankyou that has solved my problem, I can't believe it was so simple
