Excelで突合する方法｜関数8選の使い分け【VLOOKUP・XLOOKUP・INDEX+MATCH】
Excelで2つの表を突合するときに使う8種類の関数を比較。IDの有無、重複、金額差、空欄を確認する式と、関数選びの注意点を解説します。
請求一覧と台帳を見比べるとき、VLOOKUPで金額を探すのか、COUNTIFで件数を数えるのか。関数名は分かっても、どちらか片方だけを検索すると、反対側にしかない行や重複を見落とすことがあります。
まずキーの件数を調べ、1件だけ見つかった行で金額を比べます。この記事では、突合でよく使う8種類の関数を用途別に整理し、Excelへ貼り付けて試せる架空の2表で確認します。なお、Microsoft公式サポートの関数仕様ページは2026-10-06に参照しています。
Excelの突合に使う関数8種類
| 関数 | 主な用途 | 選ぶときの注意 |
|---|---|---|
| VLOOKUP | 左端の列で検索して値を返す | 完全一致は第4引数にFALSEを指定します |
| XLOOKUP | 検索列から対応する値を返す | 古いExcelとの互換性を確認します |
| INDEX + MATCH | 検索範囲と戻り範囲を分けて検索 | MATCHの一致方法を指定します |
| COUNTIF | 1条件に合う件数を数える | 重複や未登録の候補を見つけます |
| EXACT | 2つの文字列を比較する | 大文字・小文字を区別します |
| SUMIF / SUMIFS | 条件に合う値を合計する | 合計前にキー件数を確認します |
| IFERROR | エラー時に別の値を返す | 原因の見落としを防ぐため多用しません |
| CONCATENATE / CONCAT | 複数の文字列を連結する | 連結キーの衝突に注意します |
目的別の関数の使い方
1. VLOOKUP：一致する行の値を返す
検索値は範囲のいちばん左の列に置きます。完全一致で探す場合は第4引数をFALSEにします。省略時は近似一致として扱われるため、請求番号や商品コードでは明示してください。MicrosoftのVLOOKUP仕様では、検索範囲の先頭列を検索し、FALSEで完全一致を指定します。
=VLOOKUP(A2, 台帳!$A$2:$B$8, 2, FALSE)条件にアスタリスクや疑問符が含まれると、検索条件としてワイルドカードになる場合があります。コード中にそれらの文字があるデータは、通常の文字として扱えているか確認します。
2. XLOOKUP：検索範囲と戻り範囲を指定する
検索列と戻り列を個別に指定できます。既定は完全一致で、見つからないときの表示も指定できます。Microsoftの案内ではExcel 2016・2019での利用対象外とされているため、共有ブックでは相手の環境も確かめます。XLOOKUPの仕様
=XLOOKUP(A2, 台帳!$A$2:$A$8, 台帳!$B$2:$B$8, "未登録")この式は該当する最初の値を返します。検索キーが重複しているときは、どの行を採用するか決めずに金額を確定しないでください。
3. INDEX + MATCH：検索位置と戻り値を分ける
MATCHで検索範囲内の位置を調べ、INDEXでその位置の値を返します。完全一致で検索するにはMATCHの第3引数に0を指定します。列の左右関係に縛られずに検索できますが、処理が速いと一律に比較できるわけではありません。
=INDEX(台帳!$B$2:$B$8, MATCH(A2, 台帳!$A$2:$A$8, 0))4. COUNTIF：キーの有無と重複件数を数える
件数が0なら相手側にキーがなく、2以上なら重複候補です。1件かどうかを確かめてから、検索関数や金額比較に進みます。
=COUNTIF(台帳!$A$2:$A$8, A2)COUNTIFの文字列条件は大文字・小文字を区別せず、アスタリスクと疑問符をワイルドカードとして扱います。文字として検索する場合は、チルダでエスケープします。COUNTIFの使用方法
5. EXACT：大文字・小文字を区別して比較する
=EXACT(A2, B2)EXACTは文字列が同じかをTRUE/FALSEで返し、大文字・小文字を区別します。セルの表示形式の違いは比較しません。MicrosoftのEXACT仕様
6. SUMIF / SUMIFS：条件に合う金額を合計する
1条件ならSUMIF、複数条件ならSUMIFSを使います。1つのキーが複数行に当たると合計されるため、合計だけで一対一の一致と判断しないことが大切です。
=SUMIF(台帳!$A$2:$A$8, A2, 台帳!$B$2:$B$8)
=SUMIFS(台帳!$C$2:$C$100, 台帳!$A$2:$A$100, A2, 台帳!$B$2:$B$100, B2)SUMIFSの条件範囲・条件の並べ方はMicrosoftのSUMIFS仕様を参照してください。複数列をキーにして行を探す例はExcelで複数条件を突合する方法にまとめています。
7. IFERROR：エラー時の値を指定する
=IFERROR(数式, "要確認")IFERRORは数式のエラーを別の値に置き換えます。原因を直したり、未登録と重複を区別したりする関数ではありません。突合結果を一律に空欄へ変えると異常を見つけにくくなるため、まず原因を確認します。IFERRORの仕様
8. CONCATENATE / CONCAT：値を連結する
=CONCAT(A2, "-", B2)連結は補助列の作成などに使えます。ただし、たとえば「AB」と「C」、「A」と「BC」は区切りなしで連結するとどちらもABCです。区切り文字を入れても、元データに同じ文字が入れば衝突します。複数列を別々の条件として使える場合は、COUNTIFSなどで列ごとに判定してください。CONCATの仕様
2つの表で一致・差異・未登録を調べる
次の例は、関数の動きを確かめるための架空データです。請求IDと台帳IDはA001形式の文字列、金額は数値です。
「請求」シートのA1:B7に貼り付けます。
請求ID 請求金額
A001 1200
A002 2500
A003 3600
A004 1800
A005 900
A007「台帳」シートのA1:B8に貼り付けます。A005の金額は空欄、A007の請求金額は空欄で、対応する台帳金額は数値の0です。
照合ID 台帳金額
A001 1200
A002 2450
A003 3600
A003 3600
A005
A006 700
A007 0まず、請求金額と台帳金額が数値として入っているか確認します。次の式が0でなければ、金額欄に文字列が混ざっています。文字列の金額を数値に直してから照合してください。空欄は両方の関数に数えられないため、この検査では空欄として残せます。
=COUNTA(B2:B7)-COUNT(B2:B7)
=COUNTA(台帳!$B$2:$B$8)-COUNT(台帳!$B$2:$B$8)次に、請求シートのC1:G1に「台帳件数」「金額入力件数」「台帳金額合計」「請求キー件数」「判定」と入力し、2行目に次の式を入れて下へコピーします。
請求シートのC2に台帳でのキー件数を数える式を入力します。
=COUNTIF(台帳!$A$2:$A$8, A2)D2では台帳金額が空欄ではない行数を数え、E2では該当金額の合計を出します。
=COUNTIFS(台帳!$A$2:$A$8, A2, 台帳!$B$2:$B$8, "<>")=SUMIF(台帳!$A$2:$A$8, A2, 台帳!$B$2:$B$8)F2で請求側の同じIDの件数を調べ、G2で判定します。各式を指定したセルに入力し、下へコピーしてください。
=COUNTIF($A$2:$A$7, A2)=IF(F2>1, "請求で重複", IF(C2=0, "台帳に未登録", IF(C2>1, "台帳で重複", IF(D2=0, "台帳金額未入力", IF(B2="", "請求金額未入力", IF(B2=E2, "金額一致", "金額差異"))))))判定は請求側と台帳側のキー件数を確認し、金額入力の有無を見てから金額を比べます。請求キーが重複する場合も、どの請求行と対応するか確定できないため先に止めます。金額列に文字列があれば、前段の数値検査で見つけて修正します。台帳金額が空欄のA005は金額入力件数が0なので「台帳金額未入力」となります。反対に、A007の請求金額が空欄で台帳が数値の0なら「請求金額未入力」です。どちらの空欄も0円とは扱いません。
| 請求ID | 台帳件数 | 金額入力件数 | 台帳金額合計 | 請求キー件数 | 判定 |
|---|---|---|---|---|---|
| A001 | 1 | 1 | 1,200 | 1 | 金額一致 |
| A002 | 1 | 1 | 2,450 | 1 | 金額差異 |
| A003 | 2 | 2 | 7,200 | 1 | 台帳で重複 |
| A004 | 0 | 0 | 0 | 1 | 台帳に未登録 |
| A005 | 1 | 0 | 0 | 1 | 台帳金額未入力 |
| A007 | 1 | 1 | 0 | 1 | 請求金額未入力 |
台帳側でも、同じキーの重複と請求側への登録を確認します。台帳シートのC1に「台帳キー件数」、D1に「請求件数」と入力し、それぞれの列へ式を入れて下へコピーします。請求側のA007金額は空欄、台帳側は数値の0ですが、判定式では「請求金額未入力」として金額一致にしません。
=COUNTIF($A$2:$A$8, A2)=COUNTIF(請求!$A$2:$A$7, A2)| 台帳ID | 台帳キー件数 | 請求件数 | 判定の目安 |
|---|---|---|---|
| A001 | 1 | 1 | 1件ずつ存在 |
| A002 | 1 | 1 | 1件ずつ存在 |
| A003 | 2 | 1 | 台帳側の重複を確認 |
| A005 | 1 | 1 | 台帳金額の入力を確認 |
| A006 | 1 | 0 | 請求側に未登録 |
| A007 | 1 | 1 | 請求金額未入力。0円と扱わない |
重複行を見つけても、数式で片方を削除せず、元表や取引記録で理由を確認します。IDが大文字・小文字で別物になる運用ならCOUNTIFでは区別されないため、EXACTなどで確認する条件を追加してください。
突合結果が合わないときに確認すること
| 状況 | 見直すこと |
|---|---|
| IDが見つからない | 片方だけ数値、片方だけ文字列になっていないか。先頭ゼロや前後の空白、全角・半角も確認します |
| 同じに見えるのに件数が違う | COUNTIFは大文字・小文字を区別しません。ワイルドカードの*や?がIDに含まれていないか確認します |
| 金額が0と出る | 相手の金額セルが空欄ではないか。空欄と数値の0を区別する列を用意します |
| どの行を返したか分からない | キーの件数が1件か先に数えます。重複なら検索結果だけで確定しません |
| 数式のエラーが消えた | IFERRORで空欄化する前に、参照範囲・列位置・データ型を確認します |
社員番号や商品コードの00123と123を同じと扱うかは、業務上のルールで決めます。文字列と数値を式で無理に同一化せず、読み込んだ時点で両方のキー列の型をそろえます。
データ量や作業の条件に合わせて方法を選ぶ
同じ式を大量の行へ広げて更新するなら、Power Queryで大量データを突合する方法も選択肢です。納品書と請求書の品目・数量・単価を確認するときは、納品書と請求書の照合手順で証憑ごとの確認範囲を整理できます。
PDFや画像からExcelへ転記する場合、抽出結果は下書きとして扱い、元の書類と人が照合して確定します。手順はExcel転記を自動化する方法をご覧ください。
Excel関数は、同じルールで記録された表の行や値を検索・集計する道具です。どの行と何を比べたかを確認できるよう、キー件数と反対側の未登録も残しましょう。突合.com