Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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}
share|improve this question
    
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...... –  barry houdini Mar 10 '13 at 21:13
show 4 more comments

migrated from stackoverflow.com Mar 10 '13 at 10:14

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 0 down vote accepted

Try this formula (entered as array formula):

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

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.

share|improve this answer
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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