Index/Match running slow

[yoshiredz]

I have a report that has shift information day on day for a month per person (there's over 1000 people, so lots of data).
Each individual shift is on a separate row (so 30 each)
I want to sort the data in a better way... for example - see below.
I have tried using - =FILTER('Shift Report'!$D:$D,('Shift Report'!$A:$A=Output!$A7)*('Shift Report'!$G:$G=Output!$AM$2)*('Shift Report'!$C:$C=Output!AM$5),0)
and also - =INDEX('Shift Report'!$D:$D,MATCH(1,('Shift Report'!$A:$A=Output!$A7)*('Shift Report'!$C:$C=Output!AO$5)*('Shift Report'!$G:$G=Output!AM$2),0))
as this uses the criteria of the name, date and week to insert the shift time.
problem is, it is taking a long time to calculate.
is there another way I can do this?
I do have some macros in the spreadsheet, which speeds up other things, but my knowledge doesn't stretch that far.
Any help is greatly appreciated
Thank you..

Mon

Tue

Wed

Thu

Name

Hours

10/10/2022

11/10/2022

12/10/2022

13/10/2022

Start

End

Start

End

Adams, Jen

25

08:30:00​

14:30:00​

08:30:00​

14:30

Adamson, Andrew

37​

10:00:00​

18:30:00​

10:00:00​

18:30

 

[Legacy 143009]

Limiting your references to a reasonable range will boost up the performance significantly. Poor excel is trying to index all of your rows E.g. $A1:$A10000 or $D1:$D10000.
Also try XLOOKUP function. I am not good at new 365 functions but they offer many practical solutions.

 

[yoshiredz]

Limiting your references to a reasonable range will boost up the performance significantly. Poor excel is trying to index all of your rows E.g. $A1:$A10000 or $D1:$D10000.
Also try XLOOKUP function. I am not good at new 365 functions but they offer many practical solutions.

Thank you. So forgive me and sorry to sound stupid, but how do I limit my range? The full data amount is going to be a lot more than that
I will have a look at xlookup too.

 

[Legacy 143009]

Excel has 1.048.576 rows in total. How much more can your data be than 10.000? Then try, 100.000. But don't do all...

 

[yoshiredz]

Excel has 1.048.576 rows in total. How much more can your data be than 10.000? Then try, 100.000. But don't do all...

The total rows of data will be approx 36,000. So, other than doing my data in smaller amounts, there's no way I can speed up process?

 

[Legacy 143009]

Try this for the second formula:

=XLOOKUP(Output!$A7&Output!AO$5&Output!AM$2,'Shift Report'!$A:$A&'Shift Report'!$C:$C&'Shift Report'!$G:$G,'Shift Report'!$D:$D)

I am also trying for the first time. So it may not work I hope this may speed up the things!

 

[Fluff]

Just limit the number of rows to about 50,000

=FILTER('Shift Report'!$D2:$D50000,('Shift Report'!$A2:$A50000=$A7)*('Shift Report'!$G2:$G50000=$AM$2)*('Shift Report'!$C2:$C50000=AM$5),0)

 

[yoshiredz]

Try this for the second formula:

=XLOOKUP(Output!$A7&Output!AO$5&Output!AM$2,'Shift Report'!$A:$A&'Shift Report'!$C:$C&'Shift Report'!$G:$G,'Shift Report'!$D:$D)

I am also trying for the first time. So it may not work

Thanks, I will have a look and report back

 

[Fluff]

I would expect the Xlookup to be slower than what you already have. Also you should always avoid using whole column references especially in array formulae.

 

[yoshiredz]

I would expect the Xlookup to be slower than what you already have. Also you should always avoid using whole column references especially in array formulae.

Is there a better alternative that you would suggest?