Найти пересечение массива из 2 совпадений формул массива

Question

Как найти пересечение функции двух совпадений, используя формулу массива (нажатие клавиш CTR+SHIFT+ENTER).

В настоящее время я пытаюсь использовать эту формулу массива, но не могу получить никакого результата.

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

Например, данные в Sheet2 выглядят так, как показано ниже.

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

И мои данные в Sheet1 выглядят следующим образом.

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

Используя приведенную выше формулу массива, я ожидаю пересечения массива 2 (из числа строк):

= {4,5} {3,5}
= {5}

Answer 1

Попробуйте эту формулу (вводится как формула массива):

=MATCH(B2&C2,Sheet2!$A$2:$A$5&Sheet2!$B$2:$B$5,0)

Answer 2

Если вы ожидаете только одного матча, совет Питера должен делать то, что вы хотите. Если нет, то вы можете расширить решение Excellll, чтобы получить массив, который содержит только совпадающие номера строк, например, если строки 2 и 5 совпадают в обоих столбцах, эта формула вернет {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)))))

подтверждено с помощью CTRL+SHIFT+ENTER

Примечание: если это промежуточный шаг на пути к другому результату, то это может быть не лучший метод .....

Answer 3

Я могу получить массив, который включает номера строк для совпадений и фиктивное значение для несоответствий. С записью массива:

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

Я также могу получить совпадения в отдельных ячейках, но не в массиве в одной ячейке. С записью массива (в строке 2) и заполняйте до тех пор, пока не достигнете фиктивного значения:

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

Чтобы свернуть массив до размера совпадений, я думаю, вам понадобится (довольно простая) функция VBA.