Xlookup & Sumifs Slowing Down Excel File

jewkes6000

New Member
Joined
Mar 25, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I have an excel file which is running painfully slow. I believe the issues are where I have used the Xlookup and Sumifs formulas. Does anyone have any suggestions on how to speed this up? I am open to using visual basic if needed.

You can download the file at the link below. The two tabs where the issue probably lies is in the "Compare Tool" tab and the "Historical Pricing" tab. What I am trying to accomplish is to take the data from the "Historical Pricing" tab and display it in the format shown in the "Compare Tool" tab. Eventually the Compare Tool tab will have more projects (columns L through AE) where I can compare several projects side-by-side. I want to be able to simply change the project name and have all of the data below change based on that project name. The data "below" is coming from the "Historical Pricing" tab. I have used the Xlookup formula to reference the Project, the columns in row 6, and then the rows in column I, and then reference all of those items to the corresponding columns in the Historical pricing tab. The historical pricing tab will continue to grow, which is why I reference the entire columns (I'm guessing this might be one of the problems).


Any help or suggestions is much appreciated. Thank you in advance.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,689
Office Version
  1. 365
Platform
  1. Windows
Don't reference whole columns. Instead of A:A if your data will never go below say row 50,000 use something like A1:A50000. The IFERROR function will look at all 1 million plus rows and it is doing it multiply times with your forumlas.
 
Solution

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,689
Office Version
  1. 365
Platform
  1. Windows
Along with not referencing whole columns, the XLOOKUP function has an argument [if not found]. You should be able to remove the IFERROR function from your formulas and use this argument set to "".
 

jewkes6000

New Member
Joined
Mar 25, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
AhoyNC - Thank you so much. That worked wonders!

I had another related question. My worksheet is not complete. I want to take columns L through AE and copy them 9 times, thus making a total of 10 projects to compare against. This will essentially add 6 columns of Xlookup formulas for each project. However, there will be many times where you only want to compare 2 or 3 projects. In those instances, excel will still be calculating all of those formulas unnecessarily. I was thinking of using an IF formula in front of the Xlookup to say that if the cell with the project name is blank (Cell L6), then return blank and if not, then run Xlookup. For instances where the project is blank, will excel stop at the true statement? Or will it sill run the Xlookup even though it doesn't need to and vastly slow down the spread sheet?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,121
Messages
5,576,216
Members
412,708
Latest member
AD04
Top