Microsoft KB Archive/61177

{| = Excel: Finding the Value at the Intersection of Defined Ranges =
 * width="100%"|

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 &quot;Name1&quot; and &quot;Name2&quot;, 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 &quot;Account&quot; and &quot;Branch&quot; 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 &quot;Range1&quot; represents cells A1:A5, and &quot;Range2&quot; 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 &quot;Microsoft Excel User's Guide&quot; for version 1.03 or 1.50 or page 517 of the version 2.2 &quot;Microsoft Excel Reference.&quot;
 * }