Wow, Inga! That is the biggest formula I've ever seen!

First, if you ever find yourself writing a formula that's even 1/4 of that size, you should stop and find a better way.

It's very difficult to understand what a formula of this size is doing without an example file, but what I can say is:

There is a lot of redundancy in this formula with repeated IFERROR functions that you don't need. You can also use the LET function to declare variables you can then use repeatedly.

For example, taking just the first section of the formula for $P$1, you can simplify it to this:

=LET(
   LookupResult10, VLOOKUP($B23, Lookups!$C:$M, 10, FALSE),
   LookupResult11, VLOOKUP($B23, Lookups!$C:$M, 11, FALSE),
   LookupResult7, VLOOKUP($B23, Lookups!$C:$M, 7, FALSE),
   LookupResult8, VLOOKUP($B23, Lookups!$C:$M, 8, FALSE),
   LookupResult4, VLOOKUP($B23, Lookups!$C:$M, 4, FALSE),
   LookupResult5, VLOOKUP($B23, Lookups!$C:$M, 5, FALSE),
   LookupResult3, VLOOKUP($B23, Lookups!$C:$M, 3, FALSE),
   PivotData, IFERROR(GETPIVOTDATA("Sum of Hours", Pivot!$A$3, "Transaction Date", $P$1, "Personnel Desc", $B23, "Pay Code", AB$22), 0),
  

    IF(
       LookupResult10 <> "",
       IF($P$1 >= LookupResult11, (LookupResult10 + AB$20) * AB$21 * PivotData, (LookupResult7 + AB$20) * AB$21 * PivotData),
       IF(
           LookupResult7 <> "",
           IF($P$1 >= LookupResult8, (LookupResult7 + AB$20) * AB$21 * PivotData, (LookupResult4 + AB$20) * AB$21 * PivotData),
           IF(
               LookupResult4 <> "",
               IF($P$1 >= LookupResult5, (LookupResult4 + AB$20) * AB$21 * PivotData, (LookupResult3 + AB$20) * AB$21 * PivotData),
               (LookupResult3 + AB$20) * AB$21 * PivotData
           )
       )
   )
)

Explanation of Changes:

1. Defined Variables: Using
   LET

   , I defined each

   VLOOKUP

   result as a separate variable, such as

   LookupResult10

   ,

   LookupResult11

   , etc., to avoid redundant calls to the

   VLOOKUP

   function.

2. Simplified
   IFERROR

   : I wrapped the

   GETPIVOTDATA

   function in a single

   IFERROR

   at the variable definition stage (

   PivotData

   ) instead of repeatedly using it within the formula.

3. Final Calculation: The formula performs the core calculations using the pre-defined variables, significantly improving readability and efficiency.

However, there appears to be another pattern you can exploit: every criteria cell ($P$1, $Q$1, $R$1, $S$1, $T$1) is being tested to see if it's bigger than the lookup value returned, so couldn't you do a COUNTIFS against this criteria:

=COUNTIFS($P$1:$U$1,">="&VLOOKUP($B23,Lookups!$C:$M,11,FALSE))

and then multiply the count by this part:

VLOOKUP($B23,Lookups!$C:$M,10,FALSE)+AB$20)*AB$21)*(IFERROR(GETPIVOTDATA("Sum of Hours",Pivot!$A$3,"Transaction Date",$R$1,"Personnel Desc",$B23,"Pay Code",AB$22),0)))

Another option is to create separate formulas for each pattern in their own columns and then bring them together in one final formula. 

There is no shame in breaking the formula down into separate elements. In fact, this is smart formula writing as those in the know understand that Excel can calculate faster this way. Plus, it will make troubleshooting easier. 

If you're still stuck, take some time to create a dummy file where we can see your formula insitu and an explanation of how it's supposed to calculate so we can give you a more complete answer.

Mynda

Mynda, Thank you so very much.  After 2 months and a block of time set aside, I was able to use your suggestion to make it work flawlessly.  I followed your directions and used your "script" but because the original employee who set up the spreadsheet left my company I never understood what LookupResults10, LookupResults11, LookupResults7, LookupResults8. LookupResults4, LookupResults5, and LookupResults3 were?  Could you point me in the right direction of what they are so I can go learn? 

@ingaiyoung, I will try to answer, assuming it is the LookupResults names in the LET formula that you are wondering about. Even though Mynda has given a good explanation of each step.

There is a good blog article about the Excel LET function, if you haven't read it already.

The LookupResults10, 11 and so forth are only variable names. The names can be whatever you want, but it is good to have them descriptive as these are. The blog article linked to above will give more details.

If we take this line as an example, LookupResult8, VLOOKUP($B23, Lookups!$C:$M, 8, FALSE):

• LookupResults8 = Name of the variable
• VLOOKUP($B23, Lookups!$C:$M, 8, FALSE) = The value from the VLOOKUP is stored in the variable

As I don't see any attached file I don't know what the VLOOKUP is looking up, but what this formula is doing is:

• Taking the value in cell B23 and check if it is found in any row in column C in the Lookups sheet, when an exact match is found it takes the value in column J for that row. Column J is the 8th column if we start counting from column C.

I don't know if this is what you was asking about, hopefully I guessed correct.

Br,
Anders

Thank you Anders.  I have to digest what you wrote.  I did follow Mynda's link to the Let function.  I'm learning for sure.