Implicit intersection operator: @

Hi Matthew,

Thank you for the forum question.

The implicit intersection operator (@) in Excel is quite useful, especially when working with dynamic arrays. It helps ensure that a formula returns a single value rather than a range or array. Here’s a brief overview and some examples to help you understand its use:

What is the Implicit Intersection Operator (@)?
The implicit intersection operator reduces multiple values to a single value. This is particularly useful in dynamic array Excel, where formulas can return multiple values that "spill" into adjacent cells. The @ operator ensures that only one value is returned, similar to how Excel handled arrays before dynamic arrays were introduced.

Basic Examples
Simple Range Reference:

Without @: =A1:A10 returns a range of values.
With @: =@A1:A10 returns the value from the cell in the same row as the formula.
Using Functions:

INDEX Function:
Without @: =INDEX(A1:A10, B1) could return an array if B1 is a range.
With @: =@INDEX(A1:A10, B1) ensures a single value is returned.
OFFSET Function:
Without @: =OFFSET(A1, 1, 1) could return a range.
With @: =@OFFSET(A1, 1, 1) ensures a single value is returned.
Applying @ in Your Scenario
For your specific task, you can use the @ operator to ensure that the XLOOKUP function returns a single value. Here’s how you might structure your formula:

= XLOOKUP(@A2, Sheet2!Employer, Sheet2!Salary)
In this example:

@A2 ensures that the lookup value is treated as a single value.
Sheet2!Employer is the range where you are looking up the 'Employer'.
Sheet2!Salary is the range from which you want to return the 'Salary'.
Why Use @?
Using the @ operator can help avoid unexpected results when working with dynamic arrays, ensuring that your formulas behave consistently, especially when opening workbooks in different versions of Excel.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector