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}
  • Are you sure you're able to enter this function as it is typed above?
    – Peter L.
    Mar 10 '13 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 '13 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 '13 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 '13 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 '13 at 21:13

Try this formula (entered as array formula):

  • It's working great Peter. Never thought of this. Thanks :)
    – Dino
    Mar 12 '13 at 3:13

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


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:


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


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}


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.....

  • 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 '13 at 3:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.