検索条件に一致する値を取得する場合は基本的にVLOOKUP関数やINDEX+MATCH関数を使用しますが、それだけだと条件に一致する最初の値しか取得できません。(XLOOKUP関数の場合は下から検索する事もできます)
そのため、複数の値を取得するには作業列を使って少し工夫をする必要があります。
詳しくは以前紹介したゲームコレクション用データベースを参考にしてください。
今回はもう一つ実用的なテクニックとして配列数式を使う方法を紹介します。
この場合は1つの数式内で処理が完了するため作業列は使いませんが、内容によっては逆に処理に時間がかかる事もあるため、状況に応じて適切に使い分けることが必要です。
配列数式を使って条件と一致する複数の値を取得する
完成イメージ

=IFERROR(INDEX($A$3:$C$12, SMALL(IF($C$3:$C$12=$E$3, ROW($A$3:$A$12)-ROW($A$3)+1, ""), ROW($A1)),2), "")
★F3セルに上記数式を入力しF12セルまでオートフィル
E3セルに入力した検索条件(所属部署)と一致する氏名をすべて取得して表示します。
数式の解説

INDEX関数の中にSMALL関数、SMALL関数の中にIF関数がネストされています。
このように複数の関数が組み合わさった数式を理解する場合は、全て分解して内側から順番に見ていくと分かりやすいです。
IF関数の説明
IF($C$3:$C$12=$E$3, ROW($A$3:$A$12)-ROW($A$3)+1, "")
C3~C12セルの値がE3セルの値と一致するか順番に確認し、一致の場合は行番号、不一致の場合は空の値を要素とした配列を作成しています。
今回の例では[空,空,3,空,空,空,7,空,空,空]となります。
行番号は、後のINDEX関数の範囲に合わせるためC3セルが1行目となるように計算しています。
ちなみに、以下数式でもOK
IF($C$3:$C$12=$E$3, ROW($A$3:$A$12)-2, "")
配列数式を使用しており、今回の数式で最も重要な部分となります。
SMALL関数の説明
SMALL( [IF関数で作成された配列] , ROW($A1))
IF関数で作成された配列の中から、F3セルには1番目に小さい値、F4セルには2番目に小さい値・・となるように、第2引数にROW関数を使用しています。
つまり数式をF12セルまでオートフィルすることによって、条件に一致する値を最大10個まで表示することが可能となります。
INDEX関数の説明
INDEX($A$3:$C$12, [SMALL関数により出力された行番号] ,2)
INDEX関数により、SMALL関数で取得した行番号から氏名を抽出して表示します。
最後に
今回は少し高度なテクニック、配列数式を使用する方法を紹介しました。
ワンランク上を目指してスキルアップしたい方は、ぜひマスターしておきましょう。
コメント