Seeking a better formula for pulling large amounts of data with index match

[kxellis26]

I have a shared sheet that folks are able to access in sharepoint and input specific data that I pull back with index/match. I am wondering if there is a better way that doesn't bog down sharepoint/desktop app.

Certification Tracker - NEW.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2			Level 2
3	Agent	Cert Hours	Cambium	Hours	Pass Y/N	VoIP	Hours	Pass Y/N	Adv Tools	Hours	Pass Y/N	Billing	Hours	Pass Y/N	Upgrades	Hours	Pass Y/N
4	Name 1	0.00
5	Name 2	0.00
6	Name 3	0.00
7	Name 4	2.00				3/10/22	2.00	Y
8	Name 5	2.00							3/10/22	2.00	N
9	Name 6	2.00							3/10/22	2.00	Y
10	Name 7	4.00				3/9/22	2.00	N	3/10/22	2.00	Y
11	Name 8	2.00													3/10/22	2.00	Y
12	Name 9	2.00													3/10/22	2.00	N
13	Name 10	4.00				3/9/22	2.00	N							3/10/22	2.00	N
14	Name 11	0.00				3/10/22	0.00	N
Training Results

Cell Formulas
Range	Formula
C4:C14	C4	=SUM(E4,H4,K4,N4,Q4,W4,Z4,AC4)
D4:D14	D4	=IFERROR(INDEX('Training Log'!C:C,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(D$3='Training Log'!D:D),0)),"")
E4:E14	E4	=IFERROR(INDEX('Training Log'!E:E,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(D$3='Training Log'!D:D),0)),"")
F4:F14	F4	=IFERROR(INDEX('Training Log'!H:H,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(D$3='Training Log'!D:D),0)),"")
G4:G14	G4	=IFERROR(INDEX('Training Log'!C:C,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(G$3='Training Log'!D:D),0)),"")
H4:H14	H4	=IFERROR(INDEX('Training Log'!E:E,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(G$3='Training Log'!D:D),0)),"")
I4:I14	I4	=IFERROR(INDEX('Training Log'!H:H,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(G$3='Training Log'!D:D),0)),"")
J4:J14	J4	=IFERROR(INDEX('Training Log'!C:C,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(J$3='Training Log'!D:D),0)),"")
K4:K14	K4	=IFERROR(INDEX('Training Log'!E:E,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(J$3='Training Log'!D:D),0)),"")
L4:L14	L4	=IFERROR(INDEX('Training Log'!H:H,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(J$3='Training Log'!D:D),0)),"")
M4:M14	M4	=IFERROR(INDEX('Training Log'!C:C,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(M$3='Training Log'!D:D),0)),"")
N4:N14	N4	=IFERROR(INDEX('Training Log'!E:E,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(M$3='Training Log'!D:D),0)),"")
O4:O14	O4	=IFERROR(INDEX('Training Log'!H:H,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(M$3='Training Log'!D:D),0)),"")
P4:P14	P4	=IFERROR(INDEX('Training Log'!C:C,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(P$3='Training Log'!D:D),0)),"")
Q4:Q14	Q4	=IFERROR(INDEX('Training Log'!E:E,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(P$3='Training Log'!D:D),0)),"")
R4:R14	R4	=IFERROR(INDEX('Training Log'!H:H,MATCH(1,('Training Results'!B4='Training Log'!B:B)*(P$3='Training Log'!D:D),0)),"")

It works, but takes a while. Not a huge deal, but if someone has ideas on a better formula to help, I'd appreciate it.

 

[Fluff]

Simplest way to improve the performance is to limit the ranges, rather than using entire columns, like

=IFERROR(INDEX('Training Log'!C$2:C$10000,MATCH(1,('Training Results'!B4='Training Log'!B$2:B$10000)*(D$3='Training Log'!D$2:D$10000),0)),"")

 

[kxellis26]

That's kinda what I was thinking. Figured I would ask though. Thank you!

 

[Fluff]

Could you get multiple rows that match the criteria, or just one?

 

[kxellis26]

Just one.

 

[Fluff]

In that case you could also try

=FILTER('Training Log'!C$2:C$10000,(B4='Training Log'!B$2:B$10000)*(D$3='Training Log'!D$2:D$10000),"")

 

[kxellis26]

That worked great! Much better. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.