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}
since space is operator for intersection of range
is also a discovery for me)Ax
=Bx
=B2
, where x is a row number? And what do you want returned? The row number? The value?