Excelの数式についての質問

Excelについて質問です。
添付させて頂いたように、Sheet1ではチーム名と従業員番号がリストになっていてB列のセル内には複数の従業員番号が存在しており
Sheet2のB列に特定の従業員番号を入れるとその番号がHITするチーム名のみを黄色にハイライトしたところに抽出する式をA2以降に作成したいです。

IfとCountifの組み合わせで特定の文字が入ってるものを抽出、のやり方だとSheet1のデータの数が変わるごとに伸ばしたりしなければならず、このような条件下で必要なチーム名のみを抽出できる方法がございましたらお教えいただけないでしょうか。

画像は１枚までしか添付できないため、←にあるのはSheet１、→の黄色のハイライトがある方をSheet2としてご確認頂けると幸いです。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2022/10/31 17:05

こんばんは

従業員番号が必ず3桁であると決まっていれば比較が楽ですが、異なる桁数が混在していると包含関係が発生するので面倒なことになります。
（「23」と「123」が存在すると、区別が面倒という意味です）

そのようなことが無いと仮定しても良いのなら・・
添付図では1シート内に作成していますが、別シートにしたい場合は、セル参照を修正すれば良いです。
もしもできない場合は、一旦同じものをシート内に作成して、EF行をカット＆ペーストで移動すればできます。
（コピペではダメですのでご注意）

添付図では、
　・A、B列がご提示のデータ。
　・F1セルが番号を指定するセル。
と仮定しています。
365または2021環境であれば、E2セルに、
=FILTER(A2:A99,LEN(SUBSTITUTE(B2:B99,F1,""))<LEN(B2:B99),"")
を入力すれば、下方にスピルされます。


Filter関数を使えない環境の場合は、ローテクでやるしかありません。
（私の環境もこちらですが・・）
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B$2:B$99)/(F$1<>"")/(LEN(SUBSTITUTE(B$2:B$99,F$1,""))<LEN(B$2:B$99)),ROW(A1))),"")
の関数式を入力して、下方に適当なところまでフィルコピーします。

この回答へのお礼

大変参考になりました。ありがとうございました。

お礼日時：2022/10/31 17:42