Replace formulas that are slowing down Excel model significantly

Could you help me rewrite the formulas in the attached that are slowing down the calculation of my model significantly? The formulas in question are highlighted yellow on the (3) Help tabs in red (columns are AR, AS and AV). The yellow tabs are the lookup tables that the formulas on the Help tabs reference. These lookup tables are thousands of rows long. Also the formulas on the Help tabs are also thousands of rows long and are repeated many times on each tab referencing multiple years of data, which is why the calculation time of the model is now 12 seconds any time something changes.

The first thing to do is to limit your arrays:

=IF(AQ2<>0,0,IF(ISNA(VLOOKUP($F2,'Lookup Table 1'!$C:$C,1,FALSE)),IFERROR((AI2*(1-VLOOKUP(CONCATENATE('Help 1'!$G2,'Help 1'!$D2),'Lookup Table 1'!$A:$J,10,FALSE))-AP2),0),IF(ISNA(VLOOKUP($F2,'Lookup Table 1'!$C:$J,8,FALSE)),0,AI2*(1-VLOOKUP($F2,'Lookup Table 1'!$C:$J,8,FALSE))-AP2)))

Change these to something like this:

$C2:$C5000

$A2:$J5000

See if this speeds things up first.

Next thing is to try changing the VLOOKUP formulae to INDEX MATCH.

In the sheet help1 both the below formulas refer to the 2nd row of sheet Lookup Table 1.Will it be the same for all rows in Help sheet.
More examples in Help sheet and with more data will help to resolve the problem.

=VLOOKUP($F2,'Lookup Table 1'!$C:$C,1,FALSE)

=VLOOKUP(CONCATENATE('Help 1'!$G2,'Help 1'!$D2),'Lookup Table 1'!$A:$J,10,FALSE)

I have checked the formula speed ± whole column references for the formula on lookup 1, and it made no difference at all. Looking at the other formulae... there's nothing radically different between any of them, so I am surprised that they are making the sheet slow.

maybe the clue lies here: "which is why the calculation time of the model is now 12 seconds any time something changes." What OTHER formulae are there thatyou haven't told us about. Are there any/many volatile functions: TODAY(), NOW() OFFSET() INDIRECT() functions... or SUMPRODUCT using whole column ranges?

What else is going on in your real sheet?

I've uploaded a more complete model with dummy data in my post below as it's too big to share on this forum.

The formulas that I tracked that are causing a majority of the delay in calculation speed are on the red Help tabs. The specific columns where the formulas are you can find highlighted entirely yellow...they are around AV, AS, AW, etc.

I can't see any obvious problems... apart from the fact that your sheet is too big, with too much going on in it. At 20 Mb, my elderly laptop struggles to keep going.

Two comments:

Lookups run much faster on SORTED data. Is that possible?

Your formulae seem to spend a lot of time thrashing around between worksheets deciding what cells/rows to look up. I don't fully understand what you're up to as the sheet is too big and too sanitised (everything is an error or zero.... so I can't see where any answers that it might throw out would actually come from). But can you do all that decision making on the source sheet and then do a simpler lookup in your summary?

Functions in the reffered columns have VLOOKUP functions. Same functions repeat and hence repeat calculation are required. With the help of helper column this is avoided. In both the sheets Help1 and Help 2 Column EY is used as helper column and suitably functions are to be changed in columns AV in Sheet Help 1 and AS in sheet 2. Try this.

Sheet Help 1
In EY42 then drag down till end.

Please Login or Register  to view this content.

In Av42 then drag down

Please Login or Register  to view this content.

In Sheet Help 2

In EY29 then drag down

Please Login or Register  to view this content.

In AS29 then drag down

Please Login or Register  to view this content.

No change is required in Column AR in Sheet Help 2 and also in sheet Help 3.
As the file is too big it cannot be uploaded. If required fie wih only Sheets Help 1 and Help 2 will be uploaded.