ExcelのIF関数で条件一致した別シートの値を自動反映する方法！

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

別シート（商品マスタ）に登録したデータを、IF関数とVLOOKUPで条件一致したときだけ自動反映させます。
今回は
・入力した商品コードに一致する商品名と単価を、別シートのマスタから自動で引く方法（=IF(COUNTIF(…)=0,””,VLOOKUP(…))）
をゴールにします。
該当するコードがないときや入力セルが空欄のときは、#N/Aではなく「空欄」で表示されるようになります。

上の画像が完成形です。商品コードを入力するだけで、マスタの商品名と単価が自動で反映されます。

なお、エラーを出さないための書き方（IF+COUNTIF／IFERROR／IFNAの比較）と、数式を一括で入れるVBA、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. 元データになる「商品マスタ」を用意する

まずは、参照される側の元データ（商品マスタ）を用意します。
実際の業務では「商品マスタ」という別シートに作っておくのがおすすめです。
今回は1枚のシートで動きを確認できるように、入力表の下（13行目以降）にまとめて置いて説明します。
A列が商品コード、B列が商品名、C列が単価です。
商品コードは A001 のような形式で、10件登録しました。
この表を、これから入力表から参照して引っ張ってきます。
なお、参照先のシートは必ず同じブック内に用意します。
別のブックのシートを参照すると、そのブックを閉じたときにエラーになってしまうためです。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. 商品コードを入力する表を作る

次に、商品コードを入力する表を作ります。
1行目が見出しです。
A列に商品コードを入力すると、B列に商品名、C列に単価が自動で表示されるようにします。
今回はあらかじめA列（2〜11行目）に商品コードを入力しておきました。
B列とC列は、このあと数式を入れるので空のままにしておきます。
A4セルには、あえてマスタに存在しないコード（A999）も入れてあります。
うまくエラーを防げるか、あとで確認しましょう。

STEP3. IF関数とCOUNTIFで「一致したときだけ表示」する数式を入れる

B2セルに、次の数式を入力します。

=IF(COUNTIF(入力表!$A$14:$A$23,$A2)=0,"",VLOOKUP($A2,入力表!$A$14:$C$23,2,FALSE))

この数式は、「該当があれば値を返し、なければ空欄にする」という意味です。
まず COUNTIF で、入力したコードがマスタに何件あるかを数えます。
0件（＝存在しない）なら “” を返し、1件以上あれば VLOOKUP で商品名を引っ張ります。
1つの数式に「存在チェック」と「値の取得」をまとめて入れるのがポイントです。

STEP4. 単価を引く数式を入れる（列番号を3に変える）

C2セルには、B2セルと同じ数式の最後の列番号を3に変えたものを入力します。

=IF(COUNTIF(入力表!$A$14:$A$23,$A2)=0,"",VLOOKUP($A2,入力表!$A$14:$C$23,3,FALSE))

VLOOKUPの3は「範囲の左から3列目（単価）」を意味します。
商品名は2列目、単価は3列目なので、引っ張る列に合わせて番号を変えましょう。

別シートを参照するときの書き方

数式は、シート名のあとに ! を付けて シート名!範囲 の形で参照しています。
今回は同じシート内のマスタを見ているので、シート名は 入力表! になっています。
もし商品マスタを別シート（シート名：商品マスタ）に分けた場合は、入力表! の部分を 商品マスタ! に置きかえるだけです。

=IF(COUNTIF(商品マスタ!$A$2:$A$11,$A2)=0,"",VLOOKUP($A2,商品マスタ!$A$2:$C$11,2,FALSE))

=IF(商品マスタ!A1<>"", 商品マスタ!A1, "")

「参照先が空欄でなければその値を表示し、空欄なら空欄にする」という意味です。
セル1つをそのまま持ってくるだけなら、この形がもっともシンプルです。
今回のようにコードで検索して別の列を引く場合は、COUNTIFとVLOOKUPを組み合わせましょう。
シート名に空白や記号が含まれるときは ‘シート名’!範囲 のようにシングルクォーテーションで囲みます。

STEP5. 数式を下までコピーして結果を確認する

B2セルとC2セルを選び、フィルハンドルを下の11行目までドラッグしてコピーします。
すると、A列の商品コードに合わせて、B列に商品名、C列に単価が自動で表示されます。
数式の中で $（絶対参照） を付けているので、マスタの範囲はずれません。
コピーしても検索範囲が固定されているのがポイントです。
カンタンですね。

STEP6. 空欄や存在しないコードでもエラーにしない

ここで、うまくエラーを防げているか確認してみましょう。
試しにA3セルを空欄にしてみます。
すると、B3セルとC3セルはエラーではなく、きれいに空欄になりました。
これは、入力セルが空欄だと COUNTIF が0件を返すためです。
同じく、マスタに存在しない A4セル（A999） の行も、#N/Aにはならず空欄になっています。
先に存在チェックをしてからVLOOKUPするのが、#N/Aを出さないコツです。

#N/Aを防ぐ3つの方法をくらべる

VLOOKUPのエラーを防ぐ方法は、今回のIF+COUNTIF以外にもあります。
それぞれの特徴を整理しました。

VBAでIF＋VLOOKUPの数式を一括入力する

行数が多いときは、VBAで数式をまとめて入れるのがラクです。
Alt+F11でVBAエディターを開き、標準モジュールに貼り付けて実行します。

Sub SetIfVlookup()
    Dim r As Long, lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 2 To 11
        Cells(r, 2).Formula = "=IF(COUNTIF($A$14:$A$23,$A" & r & ")=0,"""",VLOOKUP($A" & r & ",$A$14:$C$23,2,FALSE))"
        Cells(r, 3).Formula = "=IF(COUNTIF($A$14:$A$23,$A" & r & ")=0,"""",VLOOKUP($A" & r & ",$A$14:$C$23,3,FALSE))"
    Next r
End Sub

タカヒロ

VLOOKUPの第4引数 FALSE は「完全一致で探す」という意味です。
TRUE（近似一致）にすると別の商品を拾ってしまうので、コード検索では必ず FALSE を付けましょう。
数式の中の $A$14:$C$23 は、マスタを別シートにした場合は 商品マスタ!$A$2:$C$11 に書き換えてくださいね。

うまく反映されないときの対処法（トラブルシュート）

実際に試すと、思ったように表示されないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使う3つの関数の概要です。
役割を分けて覚えると、数式を組み立てやすくなります。

IF関数（イフ）

=IF(COUNTIF(範囲,値)=0,"",VLOOKUP(値,範囲,列番号,FALSE))

COUNTIF関数（カウントイフ）

=COUNTIF(入力表!$A$14:$A$23, $A2)

VLOOKUP関数（ブイルックアップ）

=VLOOKUP($A2, 入力表!$A$14:$C$23, 2, FALSE)

完成

商品コードを入力するだけで、マスタから商品名と単価が自動で反映されました。
存在しないコードや空欄のときも、#N/Aではなく空欄で表示されています。
思ったとおりに動きましたか？
マスタを別シートに分けておけば、マスタを更新するだけで入力表の内容も自動で最新になります。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
先に存在チェックをする書き方を覚えておくと、#N/Aに悩まされなくなりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。