32000 rows, a formula for each day in each row, how do I make it faster?

[AustSportingPix]

Hey all, I've got an ever-growing database for my horse racing ratings. Let's call this "database".
I'm closer to 33k than 32k horses.
At the moment I import all horses through PQ, into a table, and from there, I have a workbook for each month, in that workbook I have a worksheet with a table for each day.
That table has the horse & its rating for that day.
On the "database" I have an xlookup formula for each day, if the horse runs on that day, it'll return its rating, if not it returns blank.
The problem I'm getting is it's getting really slow.
I can load it and hit refresh, it then can take 5 or 6 hours to refresh before I can even use Excel again.
The ages to save.
I'm not on a slow computer, 3600, 64 GB of RAM and m. 2 SSDs with a 3070 video card.
I asked on Reddit and was pretty much given the runaround.


Someone said to copy and paste the values once each day has been updated, the question is if new data comes in, will those values be mixed up?
I've never used access, so I'll be lost there and how would I get the look ups for each day from excel into access?

I know excel can handle a lot more data, so what are some tips?

 

[GraH]

Why not do all in PQ? Like why still using a XLOOKUP? Or, maybe load the tables in PowerPivot and link the result of PQ and the lookup table. Use measures over formulae to speed things up.

 

[AustSportingPix]

Why not do all in PQ? Like why still using a XLOOKUP? Or, maybe load the tables in PowerPivot and link the result of PQ and the lookup table. Use measures over formulae to speed things up.

I'm now trying PQ for each month at a time, I'll then each month into PQ and try that way.
The one thing I'm finding is the lookup formula seems to be slowing things right down.
I need these to find the last 100 runs for each horse.
I'm now only running these in the final workbook which is called "Ratings", but they slow right up when working

 

[AustSportingPix]

I may have fixed it.

I've sacked lookup when looking for the last 100 starts and changed to
=IFERROR(INDEX(All_Horses[@[20210909]:[20231130]],AGGREGATE(14,6,(COLUMN(All_Horses[@[20210909]:[20231130]])-COLUMN([@20210909])+1)/(All_Horses[@[20210909]:[20231130]]<>""),1)),"")
and it already feels quicker

 

[Peter_SSs]

I've sacked lookup when looking for the last 100 starts and changed to
=IFERROR(INDEX(All_Horses[@[20210909]:[20231130]],AGGREGATE(14,6,(COLUMN(All_Horses[@[20210909]:[20231130]])-COLUMN([@20210909])+1)/(All_Horses[@[20210909]:[20231130]]<>""),1)),"")
and it already feels quicker

Doesn't that just return the last non-empty value in the row from those stated columns?

If so, you could try this. Where I have 9^9 it just needs to be any number > the largest number that will ever be found in the cells. So if the numbers are always, say, 1 to 150 then you could use 200

=LOOKUP(9^9,All_Horses[@[20210909]:[20231130]])

 

[AustSportingPix]

Doesn't that just return the last non-empty value in the row from those stated columns?

If so, you could try this. Where I have 9^9 it just needs to be any number > the largest number that will ever be found in the cells. So if the numbers are always, say, 1 to 150 then you could use 200

=LOOKUP(9^9,All_Horses[@[20210909]:[20231130]])

because I have to do it for each of their last 100 starts.
so the -1 becomes -2, -3 and so on

 

[Peter_SSs]

because I have to do it for each of their last 100 starts.
so the -1 becomes -2, -3 and so on

Can you show us a small amount of sample data and expected results with XL2BB so the we can see how your data is laid out, any formulas that you have used, where your results would go etc?
Perhaps if you make the data smallish (but realistic) and show us what it (including results) would look like if you were only interested in the "last 3" instead of "last 100".