Slow Calculation Index and Match Array Formula.

[Little Penny]

I have a worksheet that uses 3 index and match array formulas
referencing two different worksheets (JNP) and (Shipped) in the same
workbook to update its information.

These referencing worksheets (JNP) and (Shipped) have 7 columns and
could have up to 3000 rows of information. My problem is whenever
information is changed in (JNP) and (Shipped) its takes a long time
for excel to update the information my main worksheet, is there
anything that can be done to speed up the calculation. I've seen it
take up to 5 minutes to finish calculation during which time excel is
basically unusable. Here are my formulas. Which can go down at most
150 rows. For Now.....



=IF(ISNA(MATCH(1,(TRIM('JNP'!$A$1:$A$2988)=TRIM(F8))*(TRIM('JNP'!$D$1:$D$2988)=TRIM(K8)),0)),"
",INDEX('Jets Jobs in
Process'!$C$1:$C$2988,MATCH(1,(TRIM('JNP'!$A$1:$A$2988)=TRIM(F8))*(TRIM('JNP'!$D$1:$D$2988)=TRIM(K8)),0)))

=IF(ISNA(MATCH(1,(TRIM(Shipped!$B$1:$B$3000)=TRIM(F8))*(TRIM(Shipped!$D$1:$D$3000)=TRIM(K8)),0)),"Not
Shipped",INDEX(Shipped!$C$1:$C$3000,MATCH(1,(TRIM(Shipped!$B$1:$B$3000)=TRIM(F8))*(TRIM(Shipped!$D$1:$D$3000)=TRIM(K8)),0)))

=IF(ISNA(MATCH(1,(TRIM(Shipped!$B$1:$B$3000)=TRIM(F8))*(TRIM(Shipped!$D$1:$D$3000)=TRIM(K8)),0)),"Not
Shipped",INDEX(Shipped!$D$1:$D$3000,MATCH(1,(TRIM(Shipped!$B$1:$B$3000)=TRIM(F8))*(TRIM(Shipped!$D$1:$D$3000)=TRIM(K8)),0)))


Thanks Little Penny

 

[Stephen Rasey]

You are repeating work in the TRIM statements. Possibly doing the trim on
all 3000 rows for each row you are trying to match. You are also repeating
work in the Match.
You must calculate intermediate results once and save them to intermediate
ranges.

Suggestion: Bring over TRIM('JNP'!$A$1:$A$3000) once into another range
"TrimmedA" in the matching worksheet. Likewise for TrimmedD
Save the result {TrimmedA=Trim(Fx:Fy)}into TrimA01.
Save the result of TrimA01*TrimmedD into TrimRow.
Finally save the Match into a range: MATCH(1,(TRimRow=TRIM(K8)),0) -->
range MatchRow
Now your formula becomes: =IF(ISNA(Matchrow),"
",INDEX('Jets Jobs in Process'!$C$1:$C$2988,Matchrow,1)

-Stephen Rasey
Houston