プロが教える店舗&オフィスのセキュリティ対策術

マスターSheetにある複数ブロックから検索値と合致するものを検索し、その隣の列を表示させたいです
マスターはプロジェクト名をドロップダウンで選択して、該当の"No."列を絞り込み表示をするドロップダウンを組んでいるため、横に並べざる得ませんでした

リストSheetのB:検索値"No."が、マスターシートのプロジェクトAになければ、B・C・・・と検索し該当する"No."が見つかれば、隣の列の"対象"を表示させるのが希望です

IFとVLOOKを繰り返すのが良いのか、他に簡単な関数があるのか知りたいです

(マスターSheet)
A       B     C       D     E       F
プロジェクトA   対象   プロジェクトB   対象   プロジェクトC    対象
No.1      ●課 No.2 ▲課 No.7 ×課
No.3      ▲課 No.5 ●課 No.6 ◆課
No.15 ◆課 No.8 ×課 No.10 ●課
No.4 ×課 No.12 ◆課 No.20 ×課
(リストSheet)
A        B      C
プロジェクト名   No.●   表示させたいセル
  ↑    ≪検索値≫    
ドロップダウンで選択

Windows7
Excel2010使用です

お知恵を貸していただけたら幸甚です

A 回答 (3件)

No.1・2です。



>マスター範囲:C列からN列・・・

「リスト」シートのH9セルに
=IF(COUNTIF(マスター!C:N,G9),INDEX(マスター!C$1:N$1000,SUMPRODUCT((マスター!C$1:N$1000=G9)*ROW(A$1:A$1000)),SUMPRODUCT((マスター!C$1:N$1000=G9)*COLUMN(A$1:L$1))+1),"")

とし、フィルハンドルで下へコピーしてみてください。

※ ここでCOLUMN関数はINDEX関数で範囲指定した何列目を返すか?という数式になりますので
必ずINDEX関数の列数分を必ずA列から指定します。

列数が多すぎてどこまでの列を指定すれば良いのか判りにくい場合は
=IF(COUNTIF(マスター!C:N,G9),INDEX(マスター!C$1:N$1000,SUMPRODUCT((マスター!C$1:N$1000=G9)*ROW(A$1:A$1000)),SUMPRODUCT((マスター!C$1:N$1000=G9)*COLUMN(C$1:N$1))+1-2),"")

でも大丈夫だと思います。

数式の説明は
G9セルと一致する列番号を見つけ出すためにSUMPRODUCT関数を使っていますが、
G9セルのデータを一致した列だけが「TRUE」=1となり、その他の列は「FALSE」=0 ですので
それに配列数式で列番号を掛け合わせるとG9セルが入っている列番号が取得できます。
その一つ右側のセルを表示させたいため、「+1」としています。

後者の数式はC列からINDEX関数で範囲指定した列数分を掛け合わせていますので
×1 からではなく、 ×3 からになってしまいます。
これでは2列ずれてしまうために「-2」としています。

尚、行番号も同じ考え方になりますので、
仮に「マスター」シートの2行目(項目行を含む)以降にデータがあっても
「A1:A1000」のように必ず1行目からINDEX関数で範囲指定した行数分を掛けないと行もずれることになります。

要は、INDEX関数の範囲指定した何行目、何列目を返したいか!というコトです。

この程度でよろしいでしょうかね。m(_ _)m
    • good
    • 0
この回答へのお礼

何から何までご指導ありがとうございます!
希望通りに動きました
知らなかった関数の使い方も教えて戴けて感謝です!

お礼日時:2018/06/22 14:06

No.1です。



>マスターの項目がプロジェクトC以降、Fまであるのですが
すなわち、L列までのデータになるのですね。

=IF(B2="","",INDEX(マスター!A$1:L$1000,SUMPRODUCT((マスター!A$1:L$1000=B2)*ROW(A$1:A$1000)),SUMPRODUCT((マスター!A$1:L$1000=B2)*COLUMN(A$1:L$1))+1))

にしてみてください。m(_ _)m
    • good
    • 0
この回答へのお礼

早速のご回答ありがとうございます
何度も後出しになって申し訳ありませんが、マスター及び表示をしたいリストの場所が帳票の一部分のため、COLUMN関数の範囲がこのままで良いのか分からなくなってしまっております
自力でコンバートしておりましたが、COLUMN関数を使用したことがないので迷走中です

実際は
マスター範囲:C列からN列
表示をさせたいリスト側の位置は8列目にタイトルが明記されており、データは9列目から存在します
F8(プロジェクト名)  G8(検査値)  H8(結果)
 プロジェクトA       No.3  ▲課 ←表示させたい値

頼りきりになって申し訳ありませんが、この場合のCOLUMN関数の範囲=マスター範囲と同じ列数で良い解釈であっているかも、ご教示いただけると幸甚です

お礼日時:2018/06/21 11:09

こんばんは!



「マスター」シートのNo.○に重複がないのであれば
「リスト」シートのB列だけの検索で対応できると思います。

↓の画像で「リスト」シートのC2セルに
=IF(B2="","",INDEX(マスター!A$1:F$1000,SUMPRODUCT((マスター!A$1:F$1000=B2)*ROW(A$1:A$1000)),SUMPRODUCT((マスター!A$1:F$1000=B2)*COLUMN(A$1:F$1))+1))

という数式を入れフィルハンドルで下へコピーしています。

※ エラー処理はしていません。m(_ _)m
「検索対象が複数あるところから抽出表示」の回答画像1
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます
マスターの項目がプロジェクトC以降、Fまであるのですがその場合も教えていただいた式で対応可能でしょうか
SUMPRODUCT((マスター!A$1:F$1000=B2)*ROW(A$1:A$1000))を式内に増やす形になるのでしょうか

お礼日時:2018/06/20 11:22

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!