Speeding up my process with Dynamic formula for Excel Online

[Mooncake1]

hi friends!

I currently am sorting my sheet like the attached page.

Basically I have a few unique numbers, want to add a comment next to it, then lookup the value against full columns where the number exists in the data table in col I&J. Then I want to add the comment to each replicate of the unique number. I have two priorities at the moment, col F is first priority, col E is second priority.

I am attaching this dynamic range to "Maxrows" which can be anywhere from 500 - 50000 rows. I'm finding my comments take a LONG time to compute via my method. Using singular reference formula rather than dynamic seemed quicker, but I don't understand why if I'm covering the same amount of rows? I would really want to use a dynamic formula for this as it saves DRASTICALLY on file size and allows for dynamic input.

It works right now, but it's slow as heck in Excel Online. Anyone got ideas on how I can speed this up? I'd love to try tackle this from a few different angles. If it helps, the data table also exists with a chronological index number next to it, so it would go 1,2,3,4,5,6,7 as it goes down if a XLOOKUP binary might help.

Please see below page.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Priority 2 List	Priority 1 List	Comments		Lookup Priority 2	Lookup Priority 1	Combined by priority		Data table List 2	Data table list 1	Maxrows:
2	2121	5332	Hello!		Hello!	Hello!	Hello!		2121	5332	39
3					Hello!	Hello!	Hello!		2121	5332
4					Hello!	Hello!	Hello!		2121	5332
5					Hello!	Hello!	Hello!		2121	5332
6					Hello!	Hello!	Hello!		2121	5332
7					Hello!	Hello!	Hello!		2121	5332
8					Hello!	Hello!	Hello!		2121	5332
9					Hello!	Hello!	Hello!		2121	5332
10		4461	See ya!			See ya!	See ya!			4461
11						See ya!	See ya!			4461
12						See ya!	See ya!			4461
13						See ya!	See ya!			4461
14						See ya!	See ya!			4461
15						See ya!	See ya!			4461
16						See ya!	See ya!			4461
17						See ya!	See ya!			4461
18						See ya!	See ya!			4461
19	4218	6838	Hi!		Hi!	Hi!	Hi!		4218	6838
20					Hi!	Hi!	Hi!		4218	6838
21					Hi!	Hi!	Hi!		4218	6838
22					Hi!	Hi!	Hi!		4218	6838
23					Hi!	Hi!	Hi!		4218	6838
24					Hi!	Hi!	Hi!		4218	6838
25					Hi!	Hi!	Hi!		4218	6838
26					Hi!	Hi!	Hi!		4218	6838
27					Hi!	Hi!	Hi!		4218	6838
28					Hi!	Hi!	Hi!		4218	6838
29					Hi!	Hi!	Hi!		4218	6838
30					Hi!	Hi!	Hi!		4218	6838
31	8904	9901	Hola!		Hola!	Hola!	Hola!		8904	9901
32					Hola!	Hola!	Hola!		8904	9901
33					Hola!	Hola!	Hola!		8904	9901
34					Hola!	Hola!	Hola!		8904	9901
35					Hola!	Hola!	Hola!		8904	9901
36	9999		Goodbye!		Goodbye!		Goodbye!		9999
37					Goodbye!		Goodbye!		9999
38					Goodbye!		Goodbye!		9999
39					Goodbye!		Goodbye!		9999
Sheet1

Cell Formulas
Range	Formula
E2:E39	E2	=IFERROR(LET(ReturnP,XLOOKUP(I2:INDEX(I:I,Maxrows),$A2:INDEX(A:A,Maxrows),$C2:INDEX(C:C,Maxrows),,0),IF(ReturnP=0,"",ReturnP)),"")
F2:F39	F2	=IFERROR(LET(ReturnP,XLOOKUP(J2:INDEX(J:J,Maxrows),$B2:INDEX(B:B,Maxrows),$C2:INDEX(C:C,Maxrows),,0),IF(ReturnP=0,"",ReturnP)),"")
G2:G39	G2	=IF(F2:INDEX(F:F,Maxrows)="",E2:INDEX(E:E,Maxrows),F2:INDEX(F:F,Maxrows))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Maxrows	=Sheet1!$K$2	E2:G2

Thanks !!!

 

[Mooncake1]

hi friends just bumping this, its really slow

 

[Mooncake1]

just following up this:

its not slow at all! I just forgot that one reference was too big! :D