Sheet Full of Complex Formulas Maxing out CPU 100%

Hello everyone,

I have a sheet that is full of formulas that search 500 records in a reference sheet, and return the record (string) that contains the exact text I am searching for. The formulas work great, however, now that I am searching all 999 rows for these matching strings, my CPU is maxing out at 100% for 15 minutes while it executes. I have attached the formula below, each cell(in the 5 columns I am populating) contains this exact formula with different cell references. (B2,B3,B4, etc)

My questions are as follows:

Am I doing something wrong here with my methodology?
Is there any way that I can improve these execution times?

Please Login or Register  to view this content.

Helper Columns... They're NOT a bad thing
Excel gives you over 16000 columns, it's ok to use a few of them.

You have this part repeated 4 times in the formula
LOOKUP(2,1/ISNUMBER(SEARCH(B5,'C:\Users\Admin\Downloads\[Risk_Excel_Export.xlsx]Sheet1'!$A$1:$A$999)),'C:\Users\Admin\Downloads\[Risk_Excel_Export.xlsx]Sheet1'!$A$1:$A$999)

That's creating 4x the amount of work for each single formula.

I would say put that part in it's own cell, say C5 for example
C5: =LOOKUP(2,1/ISNUMBER(SEARCH(B5,'C:\Users\Admin\Downloads\[Risk_Excel_Export.xlsx]Sheet1'!$A$1:$A$999)),'C:\Users\Admin\Downloads\[Risk_Excel_Export.xlsx]Sheet1'!$A$1:$A$999)

Then your formula is reduced to

=IF(ISBLANK(B5),"",TRIM(MID(C5,FIND("POLICE AGENCY THEFT REPORTED TO",C5),FIND("POLICE AGENCY PHONE #",C5)-(FIND("POLICE AGENCY THEFT REPORTED TO",C5)+5))))

And now you have this part repeated twice times
FIND("POLICE AGENCY THEFT REPORTED TO",C5)

Let's put that in D5
D5: =FIND("POLICE AGENCY THEFT REPORTED TO",C5)

Now we have
=IF(ISBLANK(B5),"",TRIM(MID(C5,D5,FIND("POLICE AGENCY PHONE #",C5)-(D5+5))))


That should be significantly more efficient.