Formula Help

Hello, I have tried all I know to do what I am trying, and it won't work. I could really use some help ASAP. I have a pivot table pulling off of the area in yellow and I am wanting to replace old...

Data Help.xlsx32.3 MB

excel

Formulas and Functions

JoeUser2004

Bronze Contributor

Mar 15, 2023

CatherineMadden Re: =IFERROR(INDEX(AH:AH,MATCH(1,(AF:AF=D2)*(AG:AG=H2),0)),"")

That performs 3+ million calculations, not including the sequential match. And it probably requires at least 48 MB of memory during evaluation.

Have you ever noticed complaints by users about "Excel stopped responding" and "Excel does not have sufficient resources"? Having too many such formulas is probably the culprit.

I am willing to bet that you don't have anything close to 1+ million rows of data.

So, even the following is much better:

=IFERROR(INDEX(AH:AH,MATCH(1,(AF$1:AF$100000=D2)*(AG$1:AG$100000=H2),0)),"")

Adjust 100000 up or down as needed to accommodate a reasonable estimate of the current and future needs.

Note: I assume that AH:AH is not a problem because Excel should only index into the range and fetch just one value in this case. But you might write AH$1:AH$100000 as well, just in case MSFT engineers stupidly load the entire range into memory before indexing into it. (sigh)

HansVogelaar
MVP
Mar 15, 2023
JoeUser2004

Hey Joe, her worksheet has more than 400000 data rows.

And I think Excel's calculation engine is smart enough to limit the calculation to the actually used range.
- JoeUser2004
  Bronze Contributor
  Mar 15, 2023
  HansVogelaar wrote: ``her worksheet has more than 400000 data rows``
  
  Okay, I didn't notice. But again, 500000 or 600000 is a lot less than 1+ million.
  
  HansVogelaar wrote: ``I think Excel's calculation engine is smart enough to limit the calculation to the actually used range.``
  
  Then explain why =ROWS(B:B=1) in A1 in a new workbook returns 1048576.

Forum Discussion

Formula Help

Resources