Excel: Finding the Value at the Intersection of Defined Ranges

 Last reviewed: November 2, 1994
Article ID: Q61177

SUMMARY

To find the values located at the intersection of two or more defined ranges, the intersection operator, one space, can be used.

For example, to find the value that is common to the ranges "Name1" and "Name2", use the following formula: 

   =Name1 Name2
The same procedure can be used to return the intersection of two defined ranges on another worksheet. In this case, the worksheet name must be used along with an exclamation point (!) to specify the other worksheet.

For example, to return the common value of the ranges "Account" and "Branch" defined on the worksheet Budget to another worksheet: 

   =Budget!Account Budget!Branch
If the intersection will return more than one value, the formula must be entered as an array formula into a range of cells equal to the number of values that will be returned.

For example, if "Range1" represents cells A1:A5, and "Range2" represents A2:C3, the common values are located in cells A2 and A3. To return the intersection values in cells D1:D2, select D1:D2 and enter the following formula in the active cell: 

   =Range1 Range2
To enter the formula as an array formula, press COMMAND+ENTER or COMMAND+RETURN on the main keyboard.

For more information on reference operators, see pages 83-84 in the "Microsoft Excel User's Guide" for version 1.03 or 1.50 or page 517 of the version 2.2 "Microsoft Excel Reference."

KBCategory: kbother
KBSubcategory:

Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.