Excel 2010 Copy Paste Complex Formula Takes Forever

[mrstaggart]

I have to add rounding to one VLOOKUP in a complicated formula in a price-determination worksheet and copy it to 4000 non-consecutive rows. When I do this, it takes approx. 25 seconds per cell, estimating 24 hours for all. Is there a better way? I can't use search-and-replace because of the change in cell references.

Any advice would be appreciated. The formula below is in column J of the attached.

I need to make the second line of the following read
IF($S14="PRICED",ROUND(VLOOKUP(T14,NEWPRICE_List,2,FALSE)*$F14,2):

=IF(BusinessFlag="Company1", "",
IF($S14="PRICED",VLOOKUP(T14,NEWPRICE_List,2,FALSE)*$F14,
IF(AND($S14="NOT PRICED",INDEX($S6:$S13,MATCH($P14,$P6:$P13,0),1)="NOT PRICED"),"NOT PRICED",
IF(AND($S14="NOT PRICED",INDEX($S6:$S13,MATCH($P14,$P6:$P13,0),1)="PRICED", ISERROR(VLOOKUP(T14, NEWPRICE_List, 2, FALSE)), ISERROR(VLOOKUP(T14, CurrentPriceList, 2, FALSE))), ROUND(VLOOKUP(T14,CompanyListPrice,2,FALSE)*$F14,2),
IF(AND($S14="NOT PRICED",INDEX($S6:$S13,MATCH($P14,$P6:$P13,0),1)="PRICED", ISERROR(VLOOKUP(T14, LCA_Broadcast_List, 2, FALSE))), ROUND(VLOOKUP(T14, CurrentPriceList,2,FALSE)*$F14,2),
IF(AND($S14="NOT PRICED",INDEX($S6:$S13,MATCH($P14,$P6:$P13,0),1)="PRICED",VLOOKUP(T14,NEWPRICE_List,2,FALSE)>0),ROUND(VLOOKUP(T14,NEWPRICE_List,2,FALSE)*$F14,2),
"Not Priced"))))))

 

[mrstaggart]

I've asked this question before and didn't follow forum rules and search even through my own posts to find an answer. I will withdraw this in deference to previously posted recommendations.

Sept 2012 - Thread68-1693959

 

[SkipVought]

hi,

Since you use MATCH in more than one INDEX formula, I'd suggest adding a column for the MATCH that will execute ONE TIME per row and then each INDEX can reference that column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrstaggart]

While the replacement of the repeated INDEX & MATCH formulas still took forever for all 4,000 rows, all following updates to the same formulas takes much less time. So, while I love the INDEX & MATCH formulas, with their relative references, I will be more careful about inefficiency in the future.

Thank you!