Forum Discussion
Bug in Compatibility Checker
I think this is a bug but want to check with some experts.
=INDEX($A2:$D2,1,$A$9) is being reported as a compatibility issue against version 2019.
Because this is being reported as a compatibility issue against version 2019 I'm thinking it is due to INDEX possibly returning an array function with the new calc engine. However, I can't think of any way with the 2nd argument being a constant and the 3 argument not referencing a range (e.g. $A$9:$A$10) and not referencing a spilled array (e.g. $A$9#) that this can return an array and thus is being inaccurately reported as a compatibility issue.
1 Reply
_K_O_ Excel isn't perfect at knowing when things like this will or won't be arrays. The error is coming because Excel isn't sure if this will be a spilled dynamic array or not, and so it's erring on the side of caution. You can see INDEX on Microsoft's list of functions where it will default to adding an @ in this article: https://support.microsoft.com/en-gb/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34?ui=en-us&rs=en-gb&ad=gb
