Forum Discussion
CatherineMadden
Mar 15, 2023Brass Contributor
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 with new data but I can't get the formula to work. I want to replace the Points in column I with the points in AH based on the OP# in D and date in H (shaded in blue). The rest of the data I want to remain the same, unless you see a better way for columns D or H to help with the formulas in I or J. This is what I am trying to use but isn't working. You can improve this one or use another, whichever works best.
=IFERROR(XLOOKUP($D2 &"="& @$H2, $AF:$AF &"="& $AG:$AG, $AH"$AH),"")
Attached is the tab I am needing help with. Thank you in advance.
- JoeUser2004Bronze Contributor
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)
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.
- JoeUser2004Bronze Contributor
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.
That would be
=IFERROR(INDEX(AH:AH,MATCH(1,(AF:AF=D2)*(AG:AG=H2),0)),"")
but it returns few non-blank results...
- CatherineMaddenBrass ContributorYeah it is still not working and I have tried all suggestions you all have givne me.