シートをまたぎ特定の値を返したい（検索条件が２つ）

入力シートとデータシートがあります。
下記入力シートのI列に記載する関数がわかりません。
（例として、I２セルに入力する式を教えて頂けたらと思います）

入力シートのB列には日付が、E列（例えばE2）にはIDが、G列（例えばG2）には出向先名称、I列（例えばI2）には出向先住所欄があります。

データシートのA列にはIDが、CI列に出向先名称A、CJ列に出向先住所A、CM列に出向先名称B、CN列に出向先住所Bがあります。（出向先名称は、”自宅”とか”施設○◯”とかです）

入力シートにあるIDと出向先名称をキーとして、入力シートI列にデータシートCJ列またはCN列の住所が入るようにしたいです。なお、入力シートのB列（日付）が空欄の場合はI列も空欄で表示させたいです。また、実施予定がキャンセルとなった場合も入力シートに打ち込む関係上、入力シートのG列（本来は出向先名称）に”休み”と入力されることがあります。この場合は、出向していないので出向先住所も表示されない仕様が望ましいです。

INDEXとMATCHまたはLOOKUPあたりを使うのかとは思いますが、検索条件が複数（IDと出向先名称）で、なおかつ返して欲しい値が検索列でない（出向先住所AまたはB）・・・。しかも出向先に”休み”と記載されたときには空欄にする必要があり悩んでおります。

質問者からの補足コメント

• 入力シートのイメージをアップします。
  B列が空欄でない時に、E列とG列の値をデータシート参照してI列に入力したいです。
  I列へ記載する内容がわかりません。
  （データシートのイメージもアップします）

  
    補足日時：2017/02/18 14:06

• データシートのイメージをアップします。
  このシートには、あらかじめ様々なデータが入力されています。
  IDを検索するのも、出向先名称を検索するのも必要です。
  まずIDで行を割り出し、出向先名称でCJ列かCN列かを判別したいです。

  
    補足日時：2017/02/18 14:09

• さっそくのご回答、有難うございます。
  ちょっと文章だけではわかりにくいかと思い、
  各シートのイメージを補足にアップしてみました。
  なにとぞ、よろしくお願いいたします！

  No.1の回答に寄せられた補足コメントです。 補足日時：2017/02/18 14:10

• 

  すごい！有難うございます！！
  なんか複雑すぎてよくわからないですが・・・。
  
  早速、教えて頂いた式で試してみましたが、うまく動きません。
  
  データシートのCI列に”自宅”、CJ列に”あああ市いいい町”と入力してある状態で、
  入力シートのG列に”自宅”と入力してみましたが、式を入れたI列は空欄になります・・・。
  （”あああ市いいい町”とI列に表示されるのが希望する結果です）
  もちろん、IDは両方のシートに入っています。
  （同様に、CM列の出向先を入力しても、CN列の住所は表示されません）
  
  ちなみに、実際に使っているシート名称は”データ”なので、
  頂いた式の”データシート”を”データ”に変えて試しました。

  No.2の回答に寄せられた補足コメントです。 補足日時：2017/02/18 23:57

• 

  ちなみに、「IDが一致する行の番号」を別のセル、例えば入力シートのQ列等に表示させる場合なら、もっと簡単になるでしょうか？あわせてご教示頂けると幸いです。

    補足日時：2017/02/19 00:01

No.3 ベストアンサー 回答者： yuji3690 回答日時： 2017/02/19 00:40

失礼しました。

MATCHを完全一致の指定にするの忘れてました。
I2=IF(B2="","",IF(G2=INDIRECT("データ!CI"&MATCH(E2,データ!A:A,FALSE)),INDIRECT("データ!CJ"&MATCH(E2,データ!A:A,FALSE)),IF(G2=INDIRECT("データ!CM"&MATCH(E2,データ!A:A,FALSE)),INDIRECT("データ!CN"&MATCH(E2,データ!A:A,FALSE)),"")))
MACTH4箇所の最後尾に｢,FALSE｣が追加されています。

Q2=MATCH(E2,データ!A:A,FALSE)
とすれば
I2=IF(B2="","",IF(G2=INDIRECT("データ!CI"&Q2),INDIRECT("データ!CJ"&Q2),IF(G2=INDIRECT("データ!CM"&Q2),INDIRECT("データ!CN"&Q2),"")))
となります。


IFとINDIRECTとMATCHの組み合わせなので、この３つについて説明しておきますね。

IF(条件式,A,B)
条件式の内容に合う場合Aを、合わない場合Bを、表示する。というものです。
IF(条件式１,IF(条件式2,A,B),C)
というふうに中に入れ込むことで、条件式1に一致して条件式2にも一致する場合A、条件式2には一致しない場合B、条件式1に一致しない場合C。というふうにIFの数だけ条件を分岐させることができます。

INDIRECT(文字や式)
()の中に入れた文字や、式の中身を計算した結果の値を、式に直接入力したように扱います。
=INDIRECT("データ!CI"&Q2)　の場合、
Q2=3であれば、　=データ!CI3　と入力しているのと同様に扱われ、データ!CI3に入っているデータが表示されます。

MATCH(検索値,検索範囲,検索タイプ)
検索値(直接入力でもセル指定でも可)が検索範囲の中で何番目のセルに入っているかを表示します。
検索タイプは指定しなくてもいいですが、その場合検索範囲が昇順に並んでいる必要があり、検索値よりも大きい（検索値を超えた）と判定された段階でその手前のセルが該当したと判断されると思います。完全一致の場合FALSEを指定する必要があり、この場合は昇順に並んでいなくても、完全一致するものがあれば最初に一致したセルの位置番号、なければエラーとなります。

この回答へのお礼

有難うございます！無事動きました！
また、関数の利用状況別の説明まで記載して頂き、とても勉強になりました。
他の列で、INDIRECTを使う場面が出てきたので、さっそく使ってみました！
少しづつでもスキルアップできたらと思います。
この度は、本当に有難うございました！！

お礼日時：2017/02/19 17:28

No.2 回答者： yuji3690 回答日時： 2017/02/18 19:56

IDは当然重複しないものとして、

Bが空白でない場合に、Eと一致するIDをデータシートのAから検索→GがデータシートのCIならデータシートのCJを，データシートのCMならデータシートのCNを、それ以外（空白、休み、入力ミス等）なら空白。
とするわけですね。

長くなるのでできればIDが一致する行の番号を表示するセルを作りたいところですが、一応1つのセルに押し込めてみます。
I2=IF(B2="","",IF(G2=INDIRECT("データシート!CI"&MATCH(E2,データシート!A:A)),INDIRECT("データシート!CJ"&MATCH(E2,データシート!A:A)),IF(G2=INDIRECT("データシート!CM"&MATCH(E2,データシート!A:A)),INDIRECT("データシート!CN"&MATCH(E2,データシート!A:A)),"")))

No.1 回答者： hallo-2007 回答日時： 2017/02/18 13:30

取りあえずたたき台ですが

E2 ID
G2 出向先名称A
=Vlooup(E2,A:CN,MATCH(G2,A1:CN1,FALSE),FALSE)
とかで。行方向と列方向を検索させては如何でしょうか。