For many years there has been something kooky going on in my Excel worksheets. It was this strange phenomenon that from time to time would rear its head.
I never had a name for it, I just knew it existed. Then one day a 3 minute video from MrExcel ended years of wonder and finally gave my ‘phenomenon’ a name.
It’s called Implicit Intersection.
Unlike the space operator which is an explicit intersection, in some situations Excel will use an implied or implicit intersection.
Let’s take a look at some examples.
Implicit Intersection Examples
Below is a list of Superheros and the number of movies made for each hero (according to Wikipedia).
Sorry, I have two boys (7 and 5) and these Superhero’s are discussed daily. So when I was thinking about what to use for my example data they were the first thing that popped in my mind! Sympathy for me will be accepted 🙂
Anyway, in column D I’ve entered the (non-array) formula =B:B+1. You can see in cell D4 that the sum is effectively B4+1, despite the fact that the formula is referencing the whole of column B.
This is implicit intersection at work.
In cell D4 Excel is (in the background) converting the formula from =B:B+1 to =B4+1 because the formula is on row 4. Row 4 is implied by the location of the formula.
Equally we can see in cell D12 that the formula returns 1 because the implied cell, B12, is empty.
VLOOKUP with Implicit Intersection
The first argument of VLOOKUP, the lookup_value, can be replaced with a whole column reference and Excel will apply the implicit intersection rule (as long as your formula is in the same row as the value you're looking up):
VLOOKUP using implicit intersection is the example Bill Jelen, aka MrExcel, gives in his video that ended my years of wonder.
COUNTIF to Find Duplicates
A common approach to finding duplicates is to use the COUNTIF function. Remember the syntax for COUNTIF is:
You can see in the image below that the formula in cell K4 uses the same range for both the ‘range’ and ‘criteria’ arguments.
The same formula is in all cells from K4 through to K12, yet the count is different depending on which row the formula resides in.
Again, implicit intersection is at work.
Note how cell K12 returns 0. Excel doesn’t know which cell is implicit because the formula is in a cell outside of the referenced range (B4:B11). In other words, the formula needs to be on a row within the range 4:11 for it to work.
BTW, we can tell a duplicate exists because the count is > 1.
Implicit Intersection also works with Named Ranges. I’ve given cells B4:B11 the named range ‘Movies’. When we enter the same formula; =Movies, in cells F4:F11, Excel returns the results based on the implicit intersection:
Excel Tables come with inbuilt named ranges called Structured References.
When you reference a cell in a Table from an adjacent column Excel will automatically use the Table’s structured references because implicit intersection can be employed. You can see the structured reference in the formula bar below:
Note: If I were to tab through the cells D2:D9 you would see the same formula in every cell.
However, if you reference a cell in a table from a cell outside of the table’s rows (2:9) you will get the regular cell references, as you can see in the formula bar below for cell D11:
Sometimes when you enter an array formula and forget to press CTRL+SHIFT+ENTER you will still get a result as opposed to an error.
This is typically implicit intersection at work. For example, let’s take the array formula below that hasn’t been entered with CTRL+SHIFT+ENTER:
We can see that it is actually calculating =SUM(9-1), whereas if entered correctly with CTRL+SHIFT+ENTER you would get 24:
We can see below in the Evaluate Formula window that when correctly entered the array formula takes a 1 off each value in column B, then adds them up:
Now, I’m not recommending you use whole column references, or even the implicit intersection willy nilly as I think it has the potential to confuse other users.
The reason I’ve written about it is to raise awareness and prevent you from going through years of wondering like I did.
What can I say….I’m nice like that 😉
The best practical use for implicit intersection I can think of is to use it with named ranges in formulas to make the formula easier to read and write. i.e. in the same way structured references work in Excel Tables.
Have you got a practical use for Implicit Intersection? Please share it in the comments below.
I'd like to thank MrExcel (Bill Jelen) for ending years of wonder about these strange goings on in my Excel worksheets. I can once again sleep at night!
If you liked this, or also know someone who is having sleepless nights about this phenomenon, please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.