How to Use Implicit Intersection Operator in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you may need to use an implicit intersection operator in Excel. In this article, I will show you how, and where to use an implicit intersection (@) operator in Excel.
However, if you are a user of the Microsoft 365 version of Excel, then most of the time you mayn’t need to use the @ operator. Now, let’s know the details about this @ operator.
Below, I have attached a screenshot as an overview of the article, representing the applications of the implicit intersection operator in Excel. You’ll learn more about the usage of the @ operator properly in the following sections of this article.

Overview of Using Implicit Intersection Operator in Excel


Download Practice Workbook

You can download the practice workbook from here:


What Is an Implicit Intersection Operator?

Suppose you have a range of data. Now, if you use the implicit intersection operator (@) in any formula for that entire range still it will give the value for a particular single cell. So, the @ operator decreases the output of a range into one single output for a cell.


4 Examples of Using Implicit Intersection Operator in Excel

Here, I will demonstrate 4 suitable and simple examples with detailed steps on how to use the implicit intersection operator in Excel. For your better understanding, I am going to use the following dataset. Which contains three columns. Those are Student ID, Math, and English. The dataset is given below.

Dataset for Using Implicit Intersection Operator


1. Applying Implicit Intersection Operator in Functions

Here, I will show you the application of the @ operator in functions. Actually, there are some functions where you must use the implicit intersection operator, even in Excel 365 version too. Moreover, if you don’t use the @ operator then you will get a warning for using it.
On the other hand, some functions don’t support the implicit intersection operator.


1.1 Use of VLOOKUP Function with Implicit Intersection Operator

Now, I will show you the utilization of the VLOOKUP function in the case of an implicit intersection operator. When you use an entire column in this VLOOKUP function then there is a chance to be of Spill Error. Thus, you should use the @ operator which will reduce the output for a single cell.
Now, for your understanding see the following example.

Suppose you want to find out the marks of English for each student in a new column named English (column G).

Fill in the Column named English.

Steps: 

  • First, write the following formula in the G5 cell.
=VLOOKUP(F:F,B:D,3,0)

Use of VLOOKUP function

🔎 Formula Breakdown:

  • Here, F:F (entire F column) is the lookup_value.
  • Then, B:D is the table_array from where the VLOOKUP function will search for values.
  • 3 is the Column_Index number. Which means it will return the marks from the English column.
  • 0 denotes the exact_match.
  • Then, press ENTER.

After pressing ENTER, you will get a notice from Microsoft Excel where the Excel will suggest you use the implicit intersection operator.

  • Then, press the Yes button to accept Microsoft Excel’s suggestion.

Suggestion from Microsoft Excel

As a result, you see the English mark of Student ID 1612010. So, the modified formula is:

=VLOOKUP(@F:F,B:D,3,0)

Using Implicit Intersection Operator with VLOOKUP

  • Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column and you will get the marks for all students.

Result for Using Implicit Intersection Operator in VLOOKUP Function

Read More: Intersection of Row and Column in Excel is Called a Cell


1.2 Implicit Intersection Operator with INDEX Function

Now, for the previous example, I will use the INDEX function with the implicit intersection operator, and let’s see what happens.

Steps: 

  • First, write the following formula in the G5 cell.
=@INDEX(B5:D12,1,3)

Use of INDEX Function

🔎 Formula Breakdown:

  • Here, B5:D12 is the reference_array from where the INDEX function will return values.
  • Then, 1 is the row_number. Which means it will return the value from the 1st row of the given array.
  • 3 is the column_number. Which means it will return the value from the English column.
  • After pressing ENTER, you will see the following suggestion from Microsoft Excel where the Excel will suggest you remove the implicit intersection operator.
  • Then, press the Yes button to accept Microsoft Excel’s suggestion.

Notice from Microsoft Excel for Ignoring Implicit Intersection Operator

Lastly, you will get marks in English for ID 1612010.

Results for Using Excel INDEX Function

  • Then, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column and you will get the marks for all students.

Use of Fill Handle Icon to Copy Formula

Read More: Intersection of Two Columns in Excel (7 Easy Methods)


2. Using Implicit Intersection Operator in Generic Formula

Here, I will show you a simple generic formula and the behavior of the implicit intersection operator.

Suppose you want to find out the total marks for all the students.

  • So, I will use the simple formula in the E5 cell.
=C5:C12+D5:D12
  • Subsequently, press ENTER, and you will get the total marks for all the students in one click.

Employing Generic Formula to Find Total Marks

  • Now, let’s use the implicit intersection operator in the J5 cell.
=@$H$5:$H$12+@$I$5:$I$12
  • Then, press ENTER, and you will get the total marks of only one student.

Here, in this formula, I have added two ranges but still, it returns single output using an @ operator. Additionally, the dollar sign ($) will fix the cell’s position.

Using Implicit Intersection Operator in Generic Formula

  • After that, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column and you will see all the student’s total marks.

Copy the Same Formula in All Cells

Read More: How to Use VBA Intersect Method in Excel (4 Practical Examples)


3. Employing Implicit Intersection Operator in Table

The most important use of implicit intersection operator is, in Excel Table. Basically, with the help of this @ sign, you can call not only any column of the table but also the entire table.
Now, let’s have the following Excel table named Table_Marks, which has those three columns Student ID, Math, and English.
At this moment, I want to know the remarks based on the Math marks of every student in the Status column.

Naming of Excel Table

  • So, I will use the IF function to find out the status based on marks in Math. Here, after writing “=IF(@ta” you will get some options including the Table_Marks.

Employing Implicit Intersection Operator in Table

  • Now, write the corresponding formula in the F5 cell.
=IF(Table_Marks[@Math]>40,"Pass","Fail")

Here, you can’t use the @ sign twice to call the table and the particular column.

Using IF Function within Excel Tabel

  • Consequently, press ENTER to get the status.

🔎 Formula Breakdown:

  • Firstly, the IF function will return an output of a given logical test. Which is whether the marks in the Math column are greater than 40 or not. Here, Table_Marks[@Math] is mentioned as the column of Math of Table_Marks. 3rd bracket secures the name of the column header.
  • Secondly, “Pass” —> when the logical test will be TRUE then it will return Pass. Basically, an Inverted Comma is a must for getting a text as the output.
  • Thirdly, “Fail”  —> denotes that when the logic fails then it will return Fail.

Use of Implicit Intersection Operator to Call Table

  • After that, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column and you will get the status for all students.

Use Similar Formula to Find All Statuses

Read More: How to Find Intersection of Two Lists in Excel (3 Easy Methods)


4. Use of Implicit Intersection Operator for Calling a Range

Another important use of the implicit intersection operator is for mentioning a range. To do so, I need to give a name of that certain range. Now, let’s see the steps below.

Steps: 

  • First, select all the data of a column and then write a name in the Functions box.
  • Then, press ENTER. Here, I have named the data having marks of math as Math.

Defining a Range with Name

  • Then, you can mention the whole range for your formula like the following one.
=IF(@Math>40,"Pass","Fail")
  • Subsequently, press ENTER.

Here, for the use of an @ operator, you will get a single output, like only the status for the particular ID 1612010.

Use of Implicit Intersection Operator for Calling a Range

🔎 Formula Breakdown:

  • Firstly, the IF function will return an output of the given logical test. Which is whether the marks of the data range named Math are greater than 40 or not. Here, @ is used to call the data range.
  • Secondly, “Pass” —> when the logical test will be TRUE then it will return Pass. Basically, an Inverted Comma is a must for getting a text as the output.
  • Thirdly, “Fail”  —> denotes that when the logic fails then it will return Fail.
  • After dragging the Fill Handle icon, you will get all the statuses.

Application of Fill Handle Icon for Copying Formula

  • On the other hand, if you don’t use the @ operator here, then you will find all the statuses at one press of the ENTER button.

Use of Defined Range in Excel 365 Version

Read More: Performing Intersection of Two Data Sets in Excel (4 Easy Ways)


How to Use Intersect Operator in Excel

There is another operator in Excel, named intersect operator. Actually, this is a single space that works as intersect operator. Now, for the below screenshot. Where I have chosen the C column then keep a space and choose the 5th row. As a result, it will return the value, where the column intersects the row.

  • So, the formula becomes:
=C4:C12 B5:D5

How to Use Intersect Operator in Excel

  • After pressing ENTER, you will get the following value.

Applying Intersect Operator to Find Marks of English


Practice Section

Now, you can practice the explained methods by yourself.

Practice Section to Use Implicit Intersection Operator in Excel


Conclusion

I hope you found this article helpful. Here, I have described how to use the implicit intersection operator in Excel. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo