Intersection Operator with Named Ranges – Excel Tips and Tricks

Hello All! We all are aware that VLOOKUP allows us to find the intersecting value of ranges or lists. It takes the value of one column and finds the corresponding value in the same row of another column. For example, VLOOKUP helps to find the telephone number of a person from the telephone directory. But, Excel book author Szilvia Juhasz says that we do not need VLOOKUP every time and introduces us Intersection Operator here.

How to Use Intersect Operator with Names Ranges

Szilvia Juhasz helps us in letting us know how to use Intersect operator instead of VLOOKUP for small tasks. Intersect operator is represented by space and is said to be an unusual operator in Excel. Let us learn how to use Intersect Operator with an example.

1 – Use Intersect Operator by Specifying Ranges

Suppose, say I have an Excel sheet which has superheroes names in Column A and related data in columns B, C, and D as PlanetsSaved, BabiesSaved, and LivesSaved respectively. If we have to find a number of BabiesSaved by Spiderman, then instead if using VLOOKUP, we can use Intersect operator.

Select any cell when you want to display the result. Now, the formula would have ranges like A6:D6 and C4:C8 separated by space and hit enter. Then, you could see the output as 45 which is the number of lives saved by Spiderman. Here, we have performed intersect operator by separating the cell ranges by spaces.

2 – Use Intersect Operator with Named Ranges

It is not possible to use cell ranges if we have a lot of data in an Excel sheet. But, Szilvia Juhasz shows that we can use Intersect operator can be used with named ranges. So, now instead of using cell ranges in the formula, we would use name ranges.

To do so, select the entire data in an Excel sheet and click on “Create from Selection” under “Formulas” tab. This would show the pop up with ‘Top Row’ and ‘Left Column’ values checked. Click ‘Ok’ and named ranges will be created.

Now to find the value of the number of BabiesSaved by Spiderman, then the formula would contain only named ranges separated by space, hence using the intersect operator. So, the formula would look like “Spiderman BabiesSaved”. Hit enter and you could see the output as 45 which is a number of BabiesSaved by Spiderman.

What’s next?

This is really an awesome tip by Szilvia Juhasz. Using this tip we have learned that we can use Intersect operator instead of VLOOKUP in some scenarios. Let us know your views and if you have anything to add, please do share with us through comments.

• Author
• Recent Posts

Sridhar Belide

Sridhar Belide is a skilled software engineer with over a decade of experience in the industry. Currently working at Wells Fargo since December 2018, he focuses on developing UI frameworks for front-end applications using React. Before joining Wells Fargo, Sridhar spent five years and four months at Tata Consultancy Services, where he held the position of IT Analyst. He also has experience as a Community Manager for Impact SoftTech Pvt. Ltd. EALP and as a Software Research and Development Engineer at NSN - Nokia Solutions and Networks. Sridhar holds a Bachelor of Technology (B.Tech.) in Computer Science from JNTUH, demonstrating his strong foundation in the field.

Latest posts by Sridhar Belide (see all)

• Edit Excel Formulas in WORD – Spreadsheet Tips and Tricks - February 17, 2022
• Using the Immediate Window in VBA - April 4, 2017
• Intersection Operator with Named Ranges – Excel Tips and Tricks - October 3, 2016