Formula Help

[CatherineMadden]

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.

[#2]

@CatherineMadden 

That would be

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

but it returns few non-blank results...

[#3]

@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)

[#4]

@Joe User 

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.

[#5]

@Hans Vogelaar  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.

@Hans Vogelaar  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.

[#6]

Yeah it is still not working and I have tried all suggestions you all have givne me.