1

I bumped into the following formula in Excel =@INDEX(B100:B110, C100) and was wondering what's the difference with =INDEX(B100:B110, C100) as they both appear to do the same thing. The use-case is to lookup the value of a ComboBox and place it in the cell with such formula.

SkyWalker
  • 13,729
  • 18
  • 91
  • 187
  • 2
    [Implicit intersection operator: @](https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34) – GSerg Jul 20 '22 at 07:28
  • 1
    In brief `@` operator return only first value from array output instead of many values. – Harun24hr Jul 20 '22 at 08:07
  • 2
    Not sure if we can call these duplicates, but related for more information see [here](https://stackoverflow.com/questions/61138029/excel-vba-how-to-add-dynamic-array-formula?noredirect=1&lq=1) and [here](https://stackoverflow.com/questions/58534445/how-to-distinguish-if-sumproduct-needs-to-be-inserted-with-cse-or-not/58536210#58536210). It will explain the `@`. – JvdV Jul 20 '22 at 08:11
  • About the 2nd part of the question: As is, your formula is not going to lookup anything. Do you rather want to use `VLOOKUP()` or combine `INDEX()` and `MATCH()`? Even `FILTER()` or `XLOOKUP()` maybe? – JvdV Jul 20 '22 at 08:15
  • @JvdV can you please provide a proper answer with example / use case? that would be much more useful for a lot of people and me :) rather than inconclusive comments. – SkyWalker Jul 20 '22 at 08:27

1 Answers1

5

As mentioned in the comments;

Theory:

The @ is called the Implicit Intersection Operator. See an extensive explaination in the official MS-documentation. Which states:

"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. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background)..."

Also:

"Generally speaking, functions that (CAN!!) return multi-cell ranges or arrays will be prefixed with @ if they were authored in an older version of Excel."

As you stated, you encountered this operator in the formula =@INDEX(B100:B110, C100). In fact, this is a textbook example as quite literally an example given at the previous linked documentation.

Note how I added the word "CAN" in the quote above. Formulae that can't return array will not be preceded by an @. In your case the author of the formula meant to return a single value (I pressume). However, Excel does not know this (or rather does not bother analyse this) and adds the @ when you open the workbook in a newer ms365 environment to make sure there is no difference in how the formula will work.

Sample:

Imagine the following sample data:

A
a
b
c

If an author in an excel version prior to 365 would write =INDEX(A1:A3,0) this would automatically be (though in the background) processed as =@INDEX(A1:A3,0); thus using implicit intersection, and return B as a result.

Opening this workbook in 365 will automatically recognize this formula and the use of implicit intersection and show =@INDEX(A1:A3,0). Would you remove the implicit intersection operator, ms365 will revert to it's standard: dynamic arrays, and thus will spill down the entire array referenced in the 1st parameter:

enter image description here

Related:

For additional information about practical encounters of the implicit intersection operator:

JvdV
  • 70,606
  • 8
  • 39
  • 70