Musiha Mahfuza Mukta
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.
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.
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).
Steps:
- First, write the following formula in the G5 cell.
=VLOOKUP(F:F,B:D,3,0)
🔎 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.
As a result, you see the English mark of Student ID 1612010. So, the modified formula is:
=VLOOKUP(@F:F,B:D,3,0)
- 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.
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.
[email protected](B5:D12,1,3)
🔎 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.
Lastly, you will get marks in English for ID 1612010.
- 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.
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.
- Now, let’s use the implicit intersection operator in the J5 cell.
[email protected]$H$5:[email protected]$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.
- 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.
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.
- 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.
- 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.
- 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.
- 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.
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.
- 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.
🔎 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.
- 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.
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
- After pressing ENTER, you will get the following value.
Practice Section
Now, you can practice the explained methods by yourself.
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.
