Range Intersection

If you have two named ranges you can use the intersection to reference individual cells.
If the named ranges exist at the workbook level the intersection formula can be used from any worksheet.
If the named ranges exist at the worksheet level the intersection formula can only be used on that particular worksheet.


Intersect Operator = Space

When you include a space character between two cell ranges this means you want to return the intersection.
This operator can be used to return the intersecting value of two overlapping cell ranges.



Single Row with Single Column

This can be used to return a single value
SS - table of data


Select cell "B6" and press equals "="
Drag the cursor over cells "A1:A4".
Press the spacebar and then drag the cursor over cells "B2:B4"
Press Enter
This formula will return the value 24


Multiple Rows and Multiple Columns (Array Formula)

This can be used to return more than one value in more than one cell.
SS


Select cells "B6:D6" and press equals "="
Drag the cursor over cells "A1:A4"
Press the spacebar and then drag the cursor over cells "B2:B4".
Press (Ctrl + Shift + Enter)
This array formula will return the values 10,20,30.


Multiple Rows and Multiple Columns (F9 values)

This can be used to return more than one value to a single cell.




With Named Ranges

Instead of using explicit cell references you could use named ranges instead.
SS - table of data


Define the following workbook named ranges
wbk_MyColumn = "F1:F20
wbk_MyRow = "A10:H10
Select cell "B6" and press equals "="
Enter the formula "=wbk_MyColumn wbk_MyRow"
Press Enter
This formula will return the value 24.
Using workbook level named ranges allows this formula to be used from any worksheet
When worksheet level named ranges are used the formula can be used on that specific worksheet.





© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext