Excelで2つの表を突き合わせて、片方にしかないデータを見つける方法（VLOOKUP・XLOOKUP・COUNTIF）

2026年10月13日公開

「先月の一覧にはあるのに、今月の一覧から消えている商品はどれか」「申込者リストのうち、入金済みリストに載っていない人は誰か」。2つの表を突き合わせる作業は、数式を1本入れて下にコピーするだけで片付きます。

以下では、表Aをシート「A」（A列にコード、B列に金額、2行目から）、表Bをシート「B」（同じくA列にコード、B列に金額）とし、表Aの空いているC列・D列に数式を入れます。

方法1：COUNTIFで「相手の表にあるか」を数える

表AのC2に次の数式を入れ、下までコピーします。

=IF(A2="","",IF(COUNTIF(B!$A:$A,A2)=0,"Bにない",""))

最初の IF(A2="","",…) は、コードが空欄の行で誤った判定を出さないための部分です。

COUNTIFは、指定した範囲の中に同じ値がいくつあるかを数えます。0なら表Bに無い行です。フィルターで「Bにない」だけを表示すれば一覧になります。逆向き（Bにあって、Aにない行）は、表B側に同じ数式を範囲を入れ替えて入れます。

COUNTIFは数値の「123」と文字列の「123」を同じものとして数えることがあり、VLOOKUP・XLOOKUPとは結果が変わる場合があります。また COUNTIF では「*」「?」がワイルドカードとして扱われます。コードにこれらの記号が含まれる場合は注意してください。XLOOKUPは既定の完全一致で記号そのものを検索できますが、VLOOKUPは FALSE を指定してもワイルドカードを解釈します。VLOOKUPで記号そのものを検索するときは、チルダ「~」を前に付けます（例：~*）。

方法2：XLOOKUPで相手の表の値を取り出す

存在の有無だけでなく、表Bの金額も並べて比べたいときは XLOOKUP が便利です。表AのD2に次の数式を入れます。

=IF(A2="","",XLOOKUP(A2,B!$A:$A,B!$B:$B,"Bにない"))

構文は XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
一致モードを省略すると完全一致になります。見つからない場合は、4番目の引数に指定した文字（ここでは「Bにない」）が表示されます。4番目を省略すると #N/A になります
取り出した金額と表Aの金額が違う行は、E2に =IF(OR(D2="",D2="Bにない"),"",IF(D2=B2,"","金額が違う")) のような式を入れて見つけます
同じコードが表Bに複数ある場合、XLOOKUPは最初に見つかった行の金額を返します。複数行の合計金額と比べたい場合は、SUMIF などを使う別の方法が必要です

XLOOKUPは Microsoft 365・Excel 2021 以降などで使えます。Excel 2019・2016 では使えないため、ファイルを渡す相手のExcelも確認してください。Googleスプレッドシートにも同じ名前の関数があり、一致モードの既定は完全一致です。

方法3：古いExcelでは VLOOKUP＋IFERROR

=IF(A2="","",IFERROR(VLOOKUP(A2,B!$A:$B,2,FALSE),"Bにない"))

4番目の引数 FALSE（または0）を必ず指定してください。省略すると近似一致（TRUE）とみなされ、並び順によっては別の行の値が返ります
VLOOKUPは、範囲のいちばん左の列から検索値を探します。コードが左端にない表では、列の順番を入れ替えるか、XLOOKUPを使います
IFERRORは見つからない場合以外のエラー（範囲の指定ミスなど）も「Bにない」と表示してしまいます。結果がすべて「Bにない」になったら、数式そのものを疑ってください
VLOOKUPも、同じコードが複数ある場合は最初に見つかった行の値を返します

「あるはずなのに見つからない」ときの確認ポイント

数値と文字列の違い：見た目が同じ「123」でも、片方が文字列（セルの左上に緑の三角）だとVLOOKUP・XLOOKUPでは一致しません。どちらかにそろえます
前後の空白：コードの前後に余分な半角スペースがあると、照合できない場合があります。=TRIM(A2) で取り除いた列を作ってから突き合わせます。TRIMは単語の間の連続した半角スペースも1つにまとめます。ただし、全角スペースや特殊な空白は除去できない場合があります
全角と半角：「ABC」と「ＡＢＣ」は異なる文字列です。Excelの照合方法によって結果が異なる場合があるため、必要なら ASC 関数などで文字幅をそろえてから比べます。ただし、全角と半角を区別する商品コードでは勝手に変換しないでください
先頭の0：CSVから読み込んだときに「00123」が「123」に変わっていることがあります（先頭の0が消えるのを防ぐ方法）
参照範囲のずれ：数式をコピーしたら範囲が動いていないか。範囲には $ を付けて固定します

確かめ方

突き合わせの結果は、件数で確かめます。表Aの件数＝「Bにある」件数＋「Bにない」件数になっているか。さらに「Bにない」と出た行を2〜3件選び、表Bを Ctrl＋F で検索して本当に無いかを目で確認すると安心です。コードの空欄、重複したコード、数値と文字列が混在するコードについても確認してください。件数の合計が一致していても、照合結果が正しいとは限りません。

関数の仕様は Microsoft・Google の公式ヘルプ（2026年10月時点）に基づいています。本番の表に使う前に、答えが分かっている小さな表で試してください。

制作・確認について：本記事の原案は生成AI（Claude）を使用して作成し、Excel・Power Queryに関する技術的な記述を別の生成AI（ChatGPT）でMicrosoft公式資料と照合しました。実際の画面表示や利用できる機能は、Excelのバージョン・OS・更新状況によって異なる場合があります。
数式を組む時間がない場合：たとえば「先月と今月の顧客リストを突き合わせ、増えた人・減った人・金額が変わった人を色分けしたい」「同じコードが複数行ある表を合計してから比べたい」といった数式は、ココナラ（有料・Excel関数の作成と修正、見積り相談制）で作成します。架空データでの検算結果つきです。

← 無料ツールに戻る　|　記事一覧