# Slow spreadsheets getting me down - converting formula to use dynamic ranges

#### kylerisi

##### Board Regular
Hi Team,

I have a very large spreadsheet which uses lots of formula's, as a result it has slowed the performance of my sheet right down, I was told that using dynamic ranges will help me speed things up,

However I am not sure how straight forward this is.

I was hoping someone could help me convert a formula to incorporate a dynamic range - I will then use this formula as a reference to convert all my other formulas.

=ArrayFormula(iferror(INDEX('Step List'!\$A:\$C,MATCH(1,('Step List'!\$A:\$A=\$A3)*('Step List'!\$B:\$B=H6),0),3)))

Any help to kick me off will be greatly appreciated

Kyle

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### bhos123

##### Well-known Member
Name your ranges as explained here : https://support.microsoft.com/en-us/kb/830287

your range names are say range1 range2 range3

use specific ranges like A1:C500 rather than A:C

=ArrayFormula(iferror(INDEX('Step List'!range1,MATCH(1,('Step List'!range2=\$A3)*('Step List'!range3=H6),0),3)))

Replies
4
Views
516
Replies
4
Views
651
Replies
5
Views
294
Replies
0
Views
300
Replies
3
Views
355

1,191,191
Messages
5,985,215
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back