How can I get rid of the "This formula can't reference its own cell" error?

I have columns that contain formulas making use of VLOOKUP. When trying to convert them to use XLOOKUP instead, it works just fine as long as the formulas are placed in a different column, but the new formulas break when they are placed in the column containing the original VLOOKUP formulas, giving the "This formula can't reference its own cell..." error.


I assume the problem is that the new formulas reference the entire column when they should be referencing only certain cells in that column (say, only the cells above). For example, you can see in the screenshot that the XLOOKUP formula references the whole of column K... How can I solve this issue?



Old VLOOKUP formula entered in column K

IF(D3≠"",IFERROR(IF(OR(D3="FEES",D3="BUY"),VLOOKUP(MAXIFS(A,A,"<"&A3,E,E3),A:K,11,FALSE)+(F3×G3+H3),VLOOKUP(MAXIFS(A,A,"<"&A3,E,E3),A:L,12,FALSE)×J3),G3×F3+H3),"")


New XLOOKUP formula that breaks when entered in column K

IF(D3≠"", IFERROR(IF(D3="BUY",XLOOKUP(MAXIFS(A,A, "<"&A3, E, E3),A,K,"",0,)+ (F3×G3 + H3), XLOOKUP(MAXIFS(A, A, "<"&A3, E, E3), A, L, "",0,) × J3), G3×F3 + H3), "")



I do not paste it in the original column.

=IF(D3≠"", IFERROR(IF(D3="BUY",XLOOKUP(MAXIFS(A,A, "<"&A3, E, E3),A,K,"",0,)+ (F3×G3 + H3), XLOOKUP(MAXIFS(A, A, "<"&A3, E, E3), A, L, "",0,) × J3), G3×F3 + H3), "")





iMac 27″, macOS 13.6

Posted on Apr 23, 2024 5:19 AM

Reply
4 replies

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

How can I get rid of the "This formula can't reference its own cell" error?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.