Implicit intersection operator


I am having difficulty understanding the content of the following webpage:


I have created a small dummy spreadsheet that uses the RandomArray and Offset functions. I am wanting to understand when I use the "@" operator. Can someone, please, open my small spreadsheet, look at the contents, and, perhaps, offer a few words of advice as to when the "@" operator should be used or is required? 


Thank you!

5 Replies


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)

Best Response confirmed by KStecyk (Contributor)


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.


Thank you for your response. I created a spreadsheet and worked through your examples, which I found helpful.

@Sergei Baklan 


Thank you for your response, including your spreadsheet. I opened your spreadsheet and followed your examples. I appreciate that you added additional information to help clarify.



@KStecyk , you are welcome, glad to help