MicrosoftI am not entirely surprised that there were issues with EVALUATE. My testing was limited to one trial that worked, both on entering the formula and in response to changes within the referenced data. On the other had, attempting to output the named formula to the grid gave a #VALUE! with the message "A value used in the formula is of the wrong data type". The SUMIFS accepted it nonetheless which is somewhat odd behaviour. The use of CSE solved the error but precludes all dynamic behaviour.
I like the UserVoice proposal well enough if one's focus is upon tabular data in which field names and primary keys could move position. A couple of intersecting XLOOKUPS would do the same job but a key word version of INDEX would be more concise.
I am more focused on array functionality in which data is properly accessed by index, so I am willing to accept
= INDEX( PresentContributions, 9, {4,5,6,7} )
and
= INDEX( PresentContributions, , 4 )
but would really want
= INDEX( PresentContributions, , {4,5,6,7} )
to return an array of ranges.
Interestingly
= SUMIFS( INDEX( PresentContributions, , {4,5,6,7} ), PresentContributions[Buyer], "Jay" )
works as a CSE formula but not DA.
Mind you, as a mathematician, I never understood why the misnamed INDEX function was ever needed
= PresentContributions( 9, {4,5,6,7} )
would be far closer to standard programming notation. I also note that it returns a #REF! error which suggests some processing has taken place. Of course, when the original spreadsheet architect states "You could do it the programmers' way but that would be tedious", what should one expect?
