We have a spreadsheet that pulls in data from multiple sources (Linked tables to JD Edwards software, ODBC to SQL Server) that last week took between 5 and 10 minutes.
This week it has suddenly started taking about 80 minutes. We've had multiple people try it, with the same results.

I've stepped through the vba code that it is executing, and I find 10 statements that are doing VLOOKUP's from one tab to another tab. In one of them it is looping through a tab with about 40k rows, looking up values against a tab that has just over 200k rows. In the other 9, it is looping through the tab with 200k rows, looking for a match in the tab that has 40k rows. It takes each vlookup statement between 5 and 10 minutes to execute today; leading to the 80 minutes total.

What I can't understand is what would have changed between last week and this week to cause this massive change in performance. We are using Office 365, the Excel Application.Version returns 16.0. One user is running Windows 10, the other two are using Windows 7 Enterprise. All systems have 8 gb RAM.

One thing that confuses me is the range of 200k rows is not sorted by the column that the vlookup is matching (with EXACT setting). I've added code in the script to resort the sheet by that column, and it still takes 80 minutes to run.

I've eliminated network issues, locking issues (we are accessing an Access 2016 database that contains the linked tables). I've put both files (spreadsheet and database) on my C: drive, and on our network drive. No difference - still 80 minutes.

None of our other spreadsheets seem to be having this issue (at least nobody has complained about the times).

This is one of the statements that is choking: SALESEOD = # of rows that have data, it is looking in the 200k tab; Master has 40k rows. The column contains the results of two columns combined (quote# - part#):

Range("CE6:CE" & bb).Formula = "=IFERROR(VLOOKUP(CD6,Master!$BV$21:$BW$" & SALESEOD & ",2,FALSE),0)"

Does anyone have any thoughts/suggestions?

Thanks
Steve