Excel(エクセル)関数の技：関数で複数データを取り出す

VLOOKUP関数で検索条件と一致したデータを取り出すことができますが、一致するデータが複数あるときには先頭だけしか取り出せません。
オートフィルタなどの機能を使えばよいのですが操作が分からない人も使うので、関数で取り出したいというケースがあります。
そのようなケースではどのようにしたら良いか・・・ということについて書いてみます。

作業列を使う

【考え方】条件と一致しているか否かを調べます(作業列を利用します)。一致しているものについては連番を付けてやります。
この連番順にデータを取り出せばよさそう・・・と考えています。

検索する値はG2セルに入力するものとします。D列を作業列として使用します。
G1セルやG4:I4セルに列見出しを入力していますが、見た目の問題だけで計算(処理)には必要ありません。無くてもかまいません。

条件と一致した行に連番を入れます。
D2セルに=IF(A2=$G$2,COUNTIF($A$2:A2,$G$2),"")と入力して、下方向へD11セルまでコピーします。
- A2セルとG2セルが同じなら、同じデータはA2セルから数えて何個目かを表示しています。
  COUNTIF($A$2:A2,$G$2)の範囲が複合参照になっています。
  A2セルとG2セルが同じでないときは空白にしています。
作業列の連番順にデータを取り出します。
G2セルは=IF(MAX($D$2:$D$11)<ROW(A1),"",INDEX(A$2:A$11,MATCH(ROW(A1),$D$2:$D$11,0)))　と入力します。
- INDEX(A$2:A$11,MATCH(ROW(A1),$D$2:$D$11,0))
  - 検索するセル範囲は$A$2:$C$11とします。
  - 作業列の1,2・・・を探すのはMATCH(ROW(A1),$D$2:$D$11,0)の部分です。
    - ROW(A1)で「1」と完全一致する行を$D$2:$D$11の範囲で捜しています。
      ROW(A1)を使うのは下方向へコピーして使うとき、連番を発生させるためです。
  - 表示する対象A$2:A$11は検索するセル範囲の1列目、2列目と右方向へコピーするごとに変化しますので、複合参照にします。
    (行番号だけを絶対参照にします)
連番より大きくなると、一致するデータは無いことになりますので、エラーが返ります。
エラーを表示しないように空白にします。
最初のIF関数、=IF(MAX($D$2:$D$11)<ROW(A1),"",の部分で一致するデータ数を超える行は空白になるようにしています。

G5セルに入力した数式を右方向、および下方向へコピーすると完成です。

G5セルに=IF(MAX($D$2:$D$11)<ROW(A1),"",INDEX(A$2:A$11,MATCH(ROW(A1),$D$2:$D$11,0)))　と入力します。
G5セルを選択し、フィルハンドルを右方向へ、I5セルまでドラッグして数式をコピーします。
G5:I5セルを選択して、フィルハンドルを下方向へドラッグして数式をコピーします。
下方向へは必要なだけコピーします。一致するデータが表示しきれるだけの行にあらかじめコピーします。

配列数式を使う

作業列を使いたくないというケースがあります。そのようなときは配列数式を利用することができます。

【考え方】条件と一致する行は何行目かを計算します。計算された行数の小さい方から順番にデータを取り出します。

G5セルに=IF(COUNTIF($A$2:$A$11,$G$2)<ROW(A1),"",INDEX(A$2:A$11,SMALL(IF($A$2:$A$11=$G$2,ROW($A$1:$A$10)),ROW(A1))))と入力して
[Shift]+[Ctrl]+[Enter]で入力を確定します。(配列数式にします)
G5セルをI5セルまでコピーし、更にG5:I5セルを下方向へ必要なだけコピーします。
G2セルの検索値を5月2日に変えた結果の例です。

INDEX(A$2:A$11,SMALL(IF($A$2:$A$11=$G$2,ROW($A$1:$A$10)),ROW(A1))))
- IF($A$2:$A$11=$G$2,ROW($A$1:$A$10))の部分でG2セルの値と完全一致するセルの行位置を調べています。
  この部分を配列で処理しています。
  - {FALSE;2;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;10}となっています。
    G2セルと同じでないときはFALSE、同じ時はROW($A$1:$A$10)から行位置を得られるようになっています。
  - 【注意】なお、データのセル範囲の行数$A$2:$A$11は10ですので、ROW($A$1:$A$10)と1～10になるようにします。
    たとえば、セル範囲がA3:A10と8行のときは、ROW($A$1:$A$8)のように一致させます。
- SMALL(・・・・・・・,ROW(A1))で、小さな値(行位置)から順番に取り出すようにしています。
最初のIF関数、=IF(COUNTIF($A$2:$A$11,$G$2)<ROW(A1),""の部分で一致するデータ数を超える行は空白になるようにしています。

　PageViewCounter
　 Counter

　Since2006/2/27

Excel(エクセル)関数の技：関数で複数データを取り出す

サンプルデータ

作業列を使う

配列数式を使う