Exact Match Lookups Slowing Down Calculations

Hello Excel people..

I have found lots of very helpful fixes and solutions over the years on this forum (thanks!), but this is my first actual post so I'm hoping you may be able to give me some suggestions.

I have several large (100MB - 300MB) Excel files that retrieve MySQL data with ODBC connections. I am struggling with the time they take to recalculate (I've had to turn automatic calculation off) - the largest ones can take up to an hour to completely recalculate even on a high spec PC with loads of RAM etc.

From what I have read the main speed issue is probably the amount of exact match lookups that get completed. I use INDEX(MATCH()) on multiple sheets, some of which have over 100,000 rows so I've been trying to replace these lookups with something less processor intensive.

I understand that this would be an option to look for a value in column A and return the corresponding value in column B:

Sort the data by column A ascending
Check that the value to search for is in column A
If so run a non-exact match (ascending) lookup to return the relevant value from the column B

Since there are multiple sheets containing the lookup data and results I've been trying to carry out the sort using formulas (rather than the Data > Sort menu).

I can easily sort the column A data in another column using SMALL(A:A,ROW()) in each cell. What I'm stuck on is how to keep the corresponding column B values alongside the original column A values (now that column A is sorted). This would be easy using an exact INDEX(MATCH()) lookup but as I mentioned, that is what I need to avoid.

Can anyone see how I can do this, or have any better suggestions on how to speed up the calculations, probably by removing the exact match lookups?

Thanks for your help!

Thanks for your speedy reply!

Some things I had read suggested that where large data is concerned, sorting is quick and non-exact match lookups are also quick - but exact match lookups take much more processing power. Hence trying to go down this route, but that info may well not be true.

I had avoided using the UI Sort as it would need to be done on multiple sheets, possibly pasting values each time as data I need to sort is already formulas referencing other sheets/cells. However maybe that is the way to go, possibly recording macro(s) to do it that way.

I'm open to all suggestions!