# Speed Up Index Match formula

#### SLConsultant

##### New Member
Hi, I have created the following formula and copied it to multiple rows & columns in a table in Excel which has greatly slowed down the recalculation time of the whole file. The purpose of the formula is to complete a staffing table based on a chosen a Staffing Method (so far I've only written the formula for 2 of the methods: Method 1 is based on a date to be hired and Method 6 is based on occupancy percentage thresholds) and the following variables:

The following variable is needed for all staffing methods:
Month End & Year (i.e. 11/30/2015) (which are the column headers in my table and there are currently 48 months to calculate for each position)

The following details are needed for Method 6 only:
Position Title (There are approximately 80 position titles, most of of which will use Method 1, approximately 10-20 positions will be staffed according to one of the other defined methods)
Level of Care (Method 6 refers to occupancy percentages for each month for a particular level of care)

=IFERROR(IF(\$D20=1,IF(M\$17>\$K20,\$I20,0),IF(\$D20=6,INDEX(INDEX((_SMT1,_SMT2,_SMT3,_SMT4,_SMT5,_SMT6,_SMT7,_SMT8,_SMT9,_SMT10),,,IF(\$B20='KA-SMT'!\$D\$18,1,IF(\$B20='KA-SMT'!\$E\$18,2,IF(\$B20='KA-SMT'!\$F\$18,3,IF(\$B20='KA-SMT'!\$G\$18,4,IF(\$B20='KA-SMT'!\$H\$18,5,IF(\$B20='KA-SMT'!\$I\$18,6,IF(\$B20='KA-SMT'!\$J\$18,7,IF(\$B20='KA-SMT'!\$K\$18,8,IF(\$B20='KA-SMT'!\$L\$18,9,IF(\$B20='KA-SMT'!\$M\$18,10))))))))))),MATCH(ROUND(INDEX(M\$4:M\$13,MATCH(\$E20,\$E\$4:\$E\$13,0)),2),'KA-SMT'!\$C\$20:\$C\$119,0)),0)),0)

In this example I have 10 named ranges which represent the 10 possible position titles that use Staffing Method 6. I was hoping to not have to customize the formulas for each position title and I still need to add the other possible staffing methods and related logic into the formula! Are there some better ways to structure this? Should I use visual basic instead (and if so how?) or should I just turn off and on the recalculation of this tab in my file (and if so what is best method for accomplishing this)?

Last edited:

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Replies
6
Views
331
Replies
6
Views
490
Replies
1
Views
349
Replies
9
Views
400
Replies
7
Views
1K

1,206,718
Messages
6,074,500
Members
446,072
Latest member
OrangeYellow

### 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.

### Which adblocker are you using?

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

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