☰

複数の Excel シートでの VLOOKUP と IF

tag-icon • tag-icon worksheet-function microsoft-excel-2013 vlookup
複数の Excel シートでの VLOOKUP と IF

ワークブックに以下のような2つのExcelシートがあります

シート1と2:

ここに画像の説明を入力してください

以下は2つのシート間のフィールドの関係です

  • EIDはEmIDと同じです
  • DepIDはDependentIDと同じです
  • DepDOBはDependentDOBと同じです

EID と EmID を比較したいと思います。一致した場合は、DepID と DependentID をもう一度比較します。DepID が空白の場合は、DepDOB と DependentDOB を比較します。これらすべてが当てはまる場合は、Sheet2 から DependentFirstName と DependentLastName を取得し、Sheet 1 の新しい列にコピーします。

私の意図した結果については、下の画像をご覧ください。

出力：

ここに画像の説明を入力してください

これを実現する方法を教えてください。VLOOKUP と IF を組み合わせる必要がある場合は、その数式を教えてください。これは私に悪夢を見せています :(

答え1

要求通りの解決策

要求された方法でこれを行う簡単な方法は、ヘルパー列を使用することです。シート 1 が出力例のようになり、列 A:F が表示され、シート 2 にも A:F が表示されているとします。各シートのヘルパー列に列 G を使用するとします。

ヘルパー列は 3 つの比較値を連結します。したがって、各シートの G2 には次の値が含まれます。

=A2&E2&F2

各シートの列に数式をコピーします。次に、これらの値を比較して必要なレコードを見つけます。

VLOOKUP では、検索列が配列の一番左にある必要があります。この制限のない INDEX と MATCH を使用すると、同じスタイルの検索を実現できます。シート 1、C2 には次の内容が含まれます。

=INDEX(sheet2!B:C,MATCH(G2,sheet2!G:G,0),1)

D2では:

=INDEX(sheet2!B:C,MATCH(G2,sheet2!G:G,0),2)

これらを列の下にコピーします。MATCH はシート 2 の行に一致するキーを見つけ、対応する名前を返します。

スクリーンショット

ヘルパー列を表示したくない場合は非表示にすることができます。DepDOB に関連付けられたキーが日付のように見えないのは、Excel が日付を保存するために使用する内部表現を使用しているためです。

よりシンプルな解決策

この場合、レコードにすでに含まれている姓と名のフィールドに入力するために必要なすべての情報が揃っています。ヘルパー列やルックアップを使用する代わりに、DepName フィールドを解析するだけで済みます。C2 は次のようになります。

=LEFT(B2,FIND(" ",B2)-1)

D2は次のようになります。

=RIGHT(B2,LEN(B2)-FIND(" ",B2))

関連情報