Board index » Excel » Slow Calculation Index and Match Array Formula.

Excel99
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


-

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



"Little Penny" <LittlePenny@hotmail.com>wrote in message





-