You are reading the article No, You’re Not Going Mad, It’s Just Excel’s Implicit Intersection updated in September 2023 on the website Happystarlongbien.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 No, You’re Not Going Mad, It’s Just Excel’s Implicit Intersection
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 ExamplesBelow 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 IntersectionThe 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 DuplicatesA common approach to finding duplicates is to use the COUNTIF function. Remember the syntax for COUNTIF is:
=COUNTIF(range, criteria)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.
Named Range
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:
Structured References
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:
Array Formulas
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:
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Download the Excel Workbook . Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.
Other UsesNow, 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.
Thanks
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!
You're reading No, You’re Not Going Mad, It’s Just Excel’s Implicit Intersection
Update the detailed information about No, You’re Not Going Mad, It’s Just Excel’s Implicit Intersection on the Happystarlongbien.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!