Forum Discussion
Implicit intersection operator
Actually it works as explained in support article and forces exactly the same implicit intersection behaviour as it is in pre-DA Excel where it is performed silently by default.
OFFSET returns range, not array. Thus second point is applicable to your situation. Since formula returns horizontal range, you may return intersection with the column, not with the row. As for example
Implicit intersection with the row returns an error since returned by formula range has multiple values in the row, actually we have no intersection here. That could be illustrated on more simple example:
And if formula returns an array, not range, implicit intersection operator returns first element of such array.
The explanation :Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value.
The new versions of Excel some formulas auto fills the values for us. But there are cases we don't need the whole set of values "guessed" by excel. You can try creating a list of values, Month Names Jan-Dec (A1:A12). Now on C1 you type the formula A1:A12. Excel will fill up all the rows down C1 with the values of your interval. But by adding "@" you will get only the first value. =@A1:A12
The same result you can replicate with some formula like =CHOOSE(1,A1:A12) and =@CHOOSE(1,A1:A12).
=OFFSET(A1,0,0,12,1) (Returns all values)
=@OFFSET(A1,0,0,12,1) (Returns only the first value)
Doesn't matter if is a Range or an array or a single value, using the "@" operator you will get as a result a single item or value.
By the way you can test your formulas =@OFFSET(G16:G21,0,0,6,1)
Thank you for your response. I created a spreadsheet and worked through your examples, which I found helpful.