【Excel】エクセルで2つのデータを照合して抽出する方法（VLOOKUP・突合・差分）

Excelで顧客名簿、商品一覧、売上データなど2つの表を照合し、一方にしかないデータや内容が異なる行を抽出したい場面は少なくありません。

手作業で突合すると見落としや転記ミスが起こりやすいため、VLOOKUP関数、XLOOKUP関数、COUNTIF関数、条件付き書式、フィルターを組み合わせる方法が役立ちます。

この記事では、1行目に見出しがあるデータを前提として、Excelで2つのデータを照合し、一致、不一致、差分を抽出する実践的な方法を解説します。

数式をコピーする範囲やエラー表示の意味も確認しながら、日常業務で使いやすい比較表を作成していきましょう。

 

エクセルで2つのデータを照合して抽出する方法【VLOOKUPによる突合】

それではまず、VLOOKUP関数を使って片方の表に存在するデータを検索し、照合結果を抽出する方法について解説していきます。

A列	B列	C列	D列
顧客ID	氏名	照合結果	抽出対象
C001	田中	一致	対象外
C002	佐藤	未登録	抽出

照合元の一覧をシート1、照合先の一覧をシート2に置く例で考えます。

シート1のA列には顧客ID、B列には氏名を入力し、シート2にもA列の顧客IDとB列の氏名がある状態にします。

 

VLOOKUP関数の基本構文

VLOOKUP関数は、指定した値を表の左端列で検索し、同じ行にある別列の値を返す関数です。

検索値には、照合したい顧客IDが入ったA2セルを指定します。

範囲には、照合先であるシート2のA列からB列を指定します。

列番号は、指定範囲の何列目を返すかを表す数字です。

今回は氏名を返すため、A列からB列までの2列目という意味で2を指定します。

最後のFALSEは完全一致検索を示す指定です。

IDやコードの突合では、原則としてFALSEを指定し、完全一致で検索することが重要です。

 

一致と未登録を表示する数式

シート1のC2セルに、次の数式を入力します。

この数式は、まずA2の顧客IDをシート2から検索します。

該当IDが見つかり、返された氏名とB2の氏名が同じなら、一致と表示されます。

同じIDはあるものの氏名が異なる場合には、内容差分と表示される仕組みです。

検索先にIDそのものがない場合、VLOOKUP関数はエラーを返します。

IFERROR関数でそのエラーを未登録に置き換えると、比較結果が読みやすくなります。

照合結果を文字で表示しておくと、後からフィルターで差分だけを抽出できます。

 

オートフィルによる数式コピー

C2セルに数式を入力した後は、セル右下に表示される小さな四角形を下方向へドラッグします。

これがオートフィルであり、行番号だけを自動で変えながら同じ数式をコピーする機能です。

検索範囲のSheet2!$A$2:$B$1000にはドル記号を付けます。

ドル記号を付けた絶対参照では、数式を下へコピーしても検索範囲がずれません。

A2やB2は行ごとに比較対象を変える必要があるため、相対参照のままにします。

 

COUNTIF関数による存在確認

続いては、値が照合先に存在するかどうかだけを素早く判定するCOUNTIF関数を確認していきます。

顧客ID	照合先の件数	判定
C001	1	存在
C002	0	未登録
C003	2	重複の確認

氏名や金額を取り出す必要がなく、IDの有無だけを比較したい場合にはCOUNTIF関数が適しています。

 

COUNTIF関数による未登録判定

シート1のC2セルに次の数式を入力すると、シート2のA列にA2のIDが何件あるかを数えられます。

結果が1なら、照合先に同じ顧客IDが1件存在する状態です。

結果が0なら、照合先には存在しません。

結果が2以上の場合は、照合先に同じIDが重複している可能性があります。

0件だけでなく2件以上も確認できる点が、単純な検索よりCOUNTIF関数の強みです。

 

IF関数を組み合わせた表示

件数をそのまま表示する代わりに、判定用の文字列を表示することもできます。

この式では、COUNTIF関数の結果が0なら未登録、それ以外なら存在と表示します。

照合結果を担当者へ共有するときは、数字よりも存在や未登録という文言のほうが理解しやすい場合があります。

ただし、重複も見つけたいときは、1と2以上を分ける式に変更しましょう。

 

データ型と空白の確認

見た目が同じIDでも、片方が文字列で、もう片方が数値になっていると一致しないことがあります。

先頭に0を含む商品コードや郵便番号は、文字列として扱うと桁落ちを防げます。

また、セル内に余分な空白があると、COUNTIF関数では別の値と判断されます。

不要な空白が疑われる場合は、TRIM関数で前後や連続した空白を整理する方法が有効です。

照合エラーが多いときは、数式を疑う前にデータ形式と空白を点検しましょう。

 

XLOOKUP関数による差分抽出

続いては、Microsoft 365やExcel 2021以降で利用できるXLOOKUP関数を使った、柔軟な突合方法を確認していきます。

A列	B列	C列
商品コード	現在価格	旧表の価格
P001	1200	1200
P002	980	900

XLOOKUP関数は、検索する列と返す列を別々に指定できるため、VLOOKUP関数よりも扱いやすい場面があります。

 

XLOOKUP関数の構文

XLOOKUP関数では、検索値、検索範囲、戻り範囲の順で指定します。

この式は、A2の商品コードをシート2のA列から探し、見つかった行のB列の価格を返します。

見つからなかった場合には、4番目の引数で指定した未登録と表示されます。

VLOOKUP関数のように列番号を数える必要がないため、列を追加した後でも数式の意図を把握しやすい特徴があります。

XLOOKUP関数は、検索列の左側にある値も返せるため、表の構成に制約を受けにくい関数です。

 

価格差分の判定

シート1のB列を現在価格、C列をXLOOKUP関数で取得した旧表の価格とします。

D2セルには、価格が変わったかを判定する次の数式を入力できます。

旧表にない商品コードは新規、価格が同じなら変更なし、異なれば価格変更と表示されます。

単に一致しない行を探すだけでなく、差分が何を意味するのかを表示できる点が実務上の利点です。

金額の差そのものを確認したい場合は、E2セルに=B2-C2と入力します。

値上げと値下げの方向まで一覧化できるため、改定内容の確認に便利です。

 

検索結果のエラー対策

XLOOKUP関数が使えないExcelのバージョンでは、VLOOKUP関数またはINDEX関数とMATCH関数の組み合わせを使います。

また、検索先に同じIDが複数ある場合、XLOOKUP関数は通常、先に見つかった1件を返します。

そのため、照合先データの重複を事前に確認しておくことが大切です。

検索値が空白の行まで数式をコピーすると、空白同士を検索したような結果になることがあります。

空白行を除外したい場合は、IF関数でA2が空白かどうかを先に判定しましょう。

空白行を空白のままに保つ数式は、一覧表の可読性を高める工夫です。

 

フィルターと条件付き書式による差分確認

続いては、照合結果を視覚的に確認し、差分だけを取り出すフィルターと条件付き書式を確認していきます。

顧客ID	氏名	照合結果
C001	田中	一致
C002	佐藤	未登録
C003	鈴木	内容差分

数式で結果を出した後は、未登録や内容差分の行を強調し、必要な行だけを表示すると確認作業が速くなります。

 

オートフィルターによる抽出

照合結果の見出しを含む表全体を選択し、データタブのフィルターをクリックします。

各見出しセルに下向きの矢印が表示されたら、照合結果列の矢印をクリックします。

一致のチェックを外し、未登録や内容差分だけにチェックを残すと、対象行だけを表示できます。

フィルターは元データを削除せずに表示を絞り込めるため、確認用の抽出に向いています。

 

条件付き書式による色分け

照合結果列を選択した状態で、ホームタブの条件付き書式をクリックします。

セルの強調表示ルールから文字列を含むセルを選び、未登録という文字列に赤系の書式を設定します。

同様に、内容差分には黄色やオレンジ系の書式を設定すると、状態の違いを一目で見分けられます。

一致に色を付けない設定にすれば、確認が必要な行だけが自然に目立ちます。

色分けは判断を補助する手段であり、最終的な抽出条件は照合結果の文字列で管理するのが安全です。

 

抽出結果の別シートへのコピー

フィルターで差分だけを表示した後、表示されている範囲を選択してコピーします。

新しいシートを作成し、貼り付けると差分一覧を別表として共有できます。

非表示行までコピーしたくない場合は、可視セルだけを選択する操作を使うとよいでしょう。

ホームタブの検索と選択から条件を選択して、可視セルを選択できます。

抽出シートには、照合日、照合元ファイル名、確認者の列を追加しておくと、後で履歴を追いやすくなります。

 

複数列を使ったデータ突合

続いては、IDだけでは判定できないケースで、複数列を組み合わせてデータを突合する方法を確認していきます。

社員番号	対象月	支給額	照合キー
E101	2026年9月	300000	E101202609

同じ社員番号でも月ごとに複数の記録がある場合などは、1列だけで検索すると別の行を誤って一致と判定するおそれがあります。

 

連結キーの作成

複数の条件を照合キーとして使うには、補助列で値を連結する方法が分かりやすいです。

たとえばA列の社員番号とB列の対象月を組み合わせる場合、D2セルに次の数式を入力します。

対象月が日付形式の場合はTEXT関数でyyyymmの形に整えると、表記の揺れを避けられます。

照合先シートにも同じ作り方で補助列を用意し、その列をVLOOKUP関数やXLOOKUP関数の検索キーにします。

複数条件の突合では、両方の表で同じ規則のキーを作ることが最重要です。

 

COUNTIFS関数による複数条件判定

補助列を作らず、複数条件に一致する件数を数えたい場合はCOUNTIFS関数を使います。

この式では、シート2のA列がA2と一致し、かつB列がB2と一致する行だけを数えます。

結果が0なら、2つの条件を同時に満たすデータは照合先にありません。

結果が1なら通常は一致、2以上なら重複の可能性があります。

部署、年月、商品区分など、業務上の識別条件が複数ある一覧で有効な方法です。

 

差分の原因を分ける判定列

複数列の比較では、どの項目が違うのかを分けて表示すると修正作業が進めやすくなります。

たとえば氏名、部署、金額をそれぞれ別の判定列で比較し、異なる項目名を表示します。

金額だけが異なるのか、部署だけが更新されていないのかを一覧で確認できます。

差分を一括で不一致とするだけではなく、修正対象の列まで特定する設計が実務向きです。

 

まとめ エクセルで2つのデータを照合して抽出する方法

エクセルで2つのデータを照合して抽出するには、まず顧客IDや商品コードなど、比較の基準となるキーを決めることが大切です。

照合先から値を取得して比較したい場合はVLOOKUP関数、または新しいExcelで使えるXLOOKUP関数が便利です。

存在するかどうかだけを確認する場合はCOUNTIF関数を使うと、未登録や重複候補を効率よく見つけられます。

数式による判定列を作り、フィルターで未登録や内容差分を絞り込む流れが、もっとも再利用しやすい方法です。

複数の条件で突合する必要がある場合は、連結した補助キーやCOUNTIFS関数を活用しましょう。

照合結果がおかしいと感じたときは、先頭の0、文字列と数値の違い、余分な空白、重複データを確認することが解決への近道です。

一度比較表を整えておけば、日々更新される名簿、在庫、売上、請求データの差分確認にも応用できます。