0

How do I find intersection of two match function by using array formula (pressing CTR+SHIFT+ENTER keys).

Currently I try using this array formula but cannot get any result.

{=MATCH(B2,Sheet2!$A$2:$A$5) MATCH(C2,Sheet2!$B$2:$B$5)}

For example, data in Sheet2 looks like below.

     A          B          C
1    Code       Type       Value
2    ID1        DIRECT     100
3    ID1        INDIRECT   130
4    ID2        DIRECT     200
5    ID2        INDIRECT   110

And my data in Sheet1 looks like the following.

     A          B          C
1    Date       Code       Type     
2    3/1/2013   ID2        INDIRECT     

By using the array formula above, I expect getting intersection of 2 array (of row numbers):

= {4,5} {3,5}
= {5}
9
  • Are you sure you're able to enter this function as it is typed above?
    – Peter L.
    Mar 10, 2013 at 8:27
  • Hi Peter, I'm unable to do that. But my common sense is, since space is operator for intersection of range then I try to applied to "array formula" function.
    – Dino
    Mar 10, 2013 at 15:54
  • Now I don't understand what do you mean by intersection. And since space is operator for intersection of range is also a discovery for me)
    – Peter L.
    Mar 10, 2013 at 16:57
  • Are you looking to find instances where Ax=Bx=B2, where x is a row number? And what do you want returned? The row number? The value?
    – Excellll
    Mar 10, 2013 at 19:57
  • Yes space is intersection operator......but that would give the intersection of two ranges - MATCH function returns a number (or error) - I can't see how you expect to get the intersection of those...... Mar 10, 2013 at 21:13

4 Answers 4

1

Try this formula (entered as array formula):

=MATCH(B2&C2,Sheet2!$A$2:$A$5&Sheet2!$B$2:$B$5,0)
1
  • It's working great Peter. Never thought of this. Thanks :)
    – Dino
    Mar 12, 2013 at 3:13
0

I can get an array that includes row numbers for matches and a dummy value for non-matches. With array entry:

=IF($A$2:$A$5=D2,IF($B$2:$B$5=E2,ROW($A$2:$A$5),2000000),2000000)

I can also get the matches in individual cells, but not in an array in one cell. With array entry (in row 2) and fill down until you hit a dummy value:

=SMALL(IF($A$2:$A$5=D2,IF($B$2:$B$5=E2,ROW($A$2:$A$5),2000000),2000000),ROW()-1)

To collapse the array to just the size of the matches, I believe you'll need a (fairly simple) VBA function.

0

If you only expect a single match then Peter's suggestion should do what you want. If not then you can extend Excellll's solution to get an array that only contains the matching row numbers, e.g. if rows 2 and 5 match on both columns this formula will return {2;5}

=SMALL(IF(($A$2:$A$5=D2)*($B$2:$B$5=E2),ROW($A$2:$A$5)),ROW(INDIRECT("1:"&SUM(($A$2:$A$5=D2)*($B$2:$B$5=E2)))))

confirmed with CTRL+SHIFT+ENTER

Note: if this is an intermediate step on the way to a different result then this may not be the best method.....

1
  • Hi Barry, yes I'm expected 1 return value. But this solution can be extended if I have non unique referece pairs. I want to vote up this, but as my reputation is below 15 I cannot do this :(
    – Dino
    Mar 12, 2013 at 3:12
0

In Excel 365, we can use array constructions. The Spill behavior means we do not have to enter Ctrl-Shift-Enter. First, setup your two separate vertical arrays. In this example, one starts in cell F2, and the second starts in M2. In a third column, enter this LET function. It will produce a new vertical array, consisting of the intersection of the two input arrays:

=LET(a, F2#, b, M2#, u, UNIQUE(VSTACK(a, b)), ua, MATCH(a, u, 0), ub, MATCH(b, u, 0), rs, IF(ua*ub, u, ""), SORT(rs))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .