VLOOKUP関数で複数ヒットをした際に全てをリストアップしたいが……(代替手段)

テーマ：VLOOKUP関数では複数ヒットに対応せず先頭のデータだけが返る

Excelのリストからデータを検索し、その情報を出力するにはVLOOKUP関数が適しています。例えば下図のI列から「セミナーリスト：101」が持つ「出席者」をB2へ出力するには次の式をセットします。出力結果は該当するレコードのうち、一番上にあるものとなります。

=VLOOKUP(A3,I2:J14,2,FALSE)

しかしながら上記の方法では一番上のデータしか出力されません。VLOOKUP関数では複数のレコードが該当していても、最上部のものしか返らないのです。

そこで複数の出力を可能にするために新関数のFILTER関数を利用します。

FILTER関数ならば複数のレコードが該当していればすべてを返すことができます。返す列は「出席者」で「セミナー番号がA2のレコード」を抽出するには以下の式をセットします。ノーヒット時・該当するレコードがない場合は「なし」を返す設定も登録しておきます。

=FILTER(J2:J14,I2:I14=A2,"なし")

あとでコピーすることを考慮し、I列とJ列のデータは絶対参照しておきます。

=FILTER($J$2:$J$14,$I$2:$I$14=A2,"なし")

4件返りました。ただしこのケースでは返り値が縦になっています。次にこれを縦並びから横並びに変更します。TRANSPOSE関数を用いる予定です。

前述の式を「TRANSPOSE()」で囲みます。以下の式へと加工します。

=TRANSPOSE(FILTER($J$2:$J$14,$I$2:$I$14=A2,"なし"))

加工後に確定すれば下図のようになります。

適切な絶対参照がなされていれば、この式をオートフィルでコピーすることができますね。

このようにFILTER関数とTRANSPOSE関数を用いることで、検索時に複数ヒットしたケースでも対応させることが可能になります。