Excel formulas in Microsoft 365 being converted to CSE array formula

Here's the solution folks for anybody else having this issue.  Just add an @ before the formula.  It has to do with what Excel thinks are dynamic arrays and legacy CSE arrays.

Description:

https://support.microsoft.com/en-us/office/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2?ui=en-us&rs=en-us&ad=us

Solution:

https://support.microsoft.com/en-us/office/excel-functions-that-return-ranges-or-arrays-7d1970e2-cbaa-4279-b59c-b9dd3900fc69?ui=en-us&rs=en-us&ad=us

The solution article lists formulas Microsoft has identified that will cause this problem.  Basically if you enter an @ before your formula it will prevent it from adding the CSE curly brackets {} in previous versions and causing your formulas to not work when somebody using an earlier version of Excel opens your file.

It looks like this has come up before but not really solved.

https://techcommunity.microsoft.com/t5/excel/dynamic-aware-excel-incorrectly-flagging-our-custom-xll/m-p/291362

Adding the @ is just a workaround, Excel is still incorrectly identifying formulas as dynamic arrays. :(

You are correct, it is the same, and using OFFSET does solve the compatibility problem for cells only looking at an adjacent cell.  But there's still something funky going on.

This is showing with the same "SPILL" compatibility issue:

v1=INDEX(INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN($C$35)),"$1",""),ROW($C$35),":",SUBSTITUTE(ADDRESS(1,COLUMN($N$35)),"$1",""),ROW($N$35)+1000)),MATCH($J39,INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN($C$35)),"$1",""),ROW($C$35),":",SUBSTITUTE(ADDRESS(1,COLUMN($C$35)),"$1",""),ROW($C$35)+1000)),0),COLUMNS($C$35:$E$35))

Simplified, it's also flagged with the compatibility issue:

v2=INDEX($C$35:$N$1035,MATCH($J39,$C$35:$C$1035,0),COLUMNS($C$35:$E$35))

Simplified more it's still flagged with the compatibility issue:

v3=INDEX($C$35:$N$1035,3,COLUMNS($C$35:$E$35))

This version is not flagged as a compatibility issue:

v4=INDEX($C$35:$N$1035,MATCH($J39,$C$35:$C$1035,0),3)

The most simple version is also not an issue:

v5=INDEX($C$35:$N$1035,3,3)

But the question is, if each of these are in effect the same formula, why are the first 3 a problem and the last two not?  It seems INDIRECT isn't the cause of the issue so I'll edit the header if I can.  But it also doesn't seem to be an issue when an array is entered since versions 4 & 5 above have arrays but are not issues.  I realize if I make the bottom half of the index array low enough I don't really need the complicated formula like in v1.  But I do need to keep the COLUMNS portion to maintain the formula if columns are inserted.  Oddly enough, if I use

=VLOOKUP($J39,$C$35:$E$1035,COLUMNS($C$35:$E$35),FALSE)

that does not show as a compatibility issue.  So I dunno what it is that's causing the hit or miss problem with compatibility.  Seems like it may be an issue with how Excel is checking for the new SPILL errors.

Maybe I'm missing something, but your example formula is exactly the same as just using

=$B41

If it is because you insert rows/cells at B41 and always want to reference the new row or cell, then just use

=OFFSET($B40,1,0)