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.
