Trying to improve speed of Index match, want to lookup "A1:A" & lastrow

Have you looked into creating named ranges that utilize the cells with data in a given row/column, and referencing those in your INDEX/MATCH instead?

I agree with McMahobt,

http://www.ozgrid.com/Excel/DynamicRanges.htm

https://www.bing.com/search?q=dynami...2d295453be0578

Originally Posted by mcmahobt

Have you looked into creating named ranges that utilize the cells with data in a given row/column, and referencing those in your INDEX/MATCH instead?

Sorry, I forgot something rather fundamental as to why I haven't done this as a named range. The column can change based on the another value.
In once instance, it can be column Q, in another it can be R.

Originally Posted by CK76

Use dynamic named range to set up lookup table/data or just have it converted to Excel table and use structured reference.

Alternately leverage other tools in Excel (such as PowerQuery etc).

If you need help in setting up, upload sample file that's representative of your data structure.

Unfortunately this site won't allow me to upload the test file I am building, as that would be easier

Originally Posted by MarvinP

I agree with McMahobt,

http://www.ozgrid.com/Excel/DynamicRanges.htm

https://www.bing.com/search?q=dynami...2d295453be0578

Yes, I know how to search, I am asking directly for help to see if there is something I have missed.

Originally Posted by OTWarrior

Unfortunately this site won't allow me to upload the test file I am building, as that would be easier

Is the site providing an error code/reason for this? The site does allow for workbook uploads. At the bottom of the page > Go Advanced > Manage Attachments

I click the attachment icon, a small line appears below but it doesn't bring up a browse dialog or similar

no error code

Can you explain what each Filter column is actually trying to "filter" for within your Main spreadsheet?

Your formulas in column Y are heavily and unnecessarily volatile. The formula in Y7 can be replaced by the simpler, non-volatile:

=INDEX(INDEX('Staff data'!$1:$1048576,,MATCH($B$4,DDlist,0)),MATCH($X:$X,'Staff data'!$Q:$Q,0))

Similarly for the formulas in column Z.

However, it is not this set of formulas which is causing you problems with calculation time, but rather those in S16:S28 and V14:V22. The formula in S16, for example, contains the portion:

COUNTIF(S15:S15,INDIRECT("'Staff data'!"&H$4&":"&H$4))

which resolves to:

COUNTIF(S15:S15,'Staff data'!C:C)

and, since you are wrapping this in an external, array-coercing INDEX function, you are effectively asking the formula to perform a count for all entries in column C of the Staff data sheet, an astonishing number of cells to be processed by a single formula.

I suggest you either make your ranges dynamic, as have suggested others. Failing that, you can always choose a 'better' upper bound than row 1,048,576; even 10,000 (which surely should be sufficient in your case?) would reduce the processing time by a factor of 100.

On top of that, I suggest you study the non-volatile formula I gave you and make similar amendments to all of your INDIRECT/ADDRESS constructions, all of which are volatile and none of which are necessary.

Regards

Originally Posted by OTWarrior

Unfortunately, cell H4 is what I am using to make this dynamic. H4 will change when user chooses an item from D4. This cannot be static.

You don't understand. I appreciate that you want the choice of column to be dynamic, but do you realise that you're forcing Excel to calculate every single cell (all 1,048,576 of them) within that dynamically-chosen column? That's what's causing your lag - try deleting those 22 formulas temporarily and see the huge difference in calculation time for your workbook.

Regards