How to use the Intersect Operator in Excel

The intersect operator in Excel is a method of finding the intersecting cell (or cells) within two cell ranges. It is unusually utilized via the space character.

The example shown above returns ’12’ (i.e. the intersecting cell of the two ranges).

If you wish to find multiple intersecting cells, you can use an array formula. Enter the formula indicating the ranges you wish to input. Hit enter, and a #VALUE! error will likely be returned.

Next, click the cell, drag and highlight other cells (in this instance, I highlighted G9 to H10 as I knew that there were four intersecting cells).

Click the formula box and use the keyboard shortcut Control + Shift + Enter.  You should now see {} around the formula and the other highlighted cells should be populated with the intersecting cell contents.

Download Excel File

 Example of Intersect Formula

Comments (No)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.