結論：前処理でそろえてから、XLOOKUPで突き合わせる
突合が合わない原因は2つです。どちらも下の手順で対処できます。
- データの形がそろっていない（見た目は同じでも、Excelには別物に見えている）
- 関数が、無いものを「ある」と答えている（定番のCOUNTIFで起きます）
突合の手順
2つの表が、同じファイルの 表1・表2 シートにある前提です。
別ファイルなら、シートを右クリック →「移動またはコピー」で1つのファイルにまとめてください。
1. 両方のシートに作業列を作って、同じ式を入れる
表の右端の空き列（ここでは Z列とします）に次の式を入れ、下までコピーします。A2 は自分のキー列の最初のセルに置き換えます。
元のデータは書き換えません。間違えたときに戻せなくなります。
=UPPER(SUBSTITUTE(SUBSTITUTE(ASC(TRIM(A2))," ",""),CHAR(160),""))必ず両方のシートに入れてください。 片方だけだと、かえって一致しなくなります。
キーが日付や金額なら、A2 を TEXT(A2,"yyyy/mm/dd") のように包みます（→ 前処理の節）。
それ以外のキーには使いません。1-2 のようなコードが日付に化けます。
2. 表1側にもう1列作って、表2にあるかを調べる
=XLOOKUP(Z2,表2!$Z$2:$Z$1000,表2!$A$2:$A$1000,"無し")Z2 が1で作った作業列、$Z$2:$Z$1000 が表2の作業列（探す範囲）、$A$2:$A$1000 が見つかったときに返す列です。1000 は行数より少し多めにします。
見つかれば表2側の元の表記が、無ければ「無し」が出ます。山田 花子 が返れば、空白の違いだったと原因も分かります。
古いExcel（Office 2019以前）は、2の代わりにこちらです（理由は「原因2」で説明します）。
=IF(SUMPRODUCT(--EXACT(表2!$Z$2:$Z$1000,Z2))=0,"無し","あり")COUNTIFは使わないでください。 無いものを「ある」と答えることがあります。
3. 差分を見て、逆向きも見る
2の列にフィルターをかけて「無し」だけを表示すれば、それが「表1にあって表2に無いもの」です。表2 シートにも同じように列を作り、2の式の 表2! を 表1! に書き換えて入れます。
逆向きは、この形でしか見つかりません。
「表2にあって表1に無いもの」は、この向きでしか見つかりません。
この記事に出てくる用語
- 突合（とつごう） … 2つの表を突き合わせて、一致するもの・しないものを見つける作業。「照合」とも言う
- キー … 2つの表を結びつける目印になる列。顧客名・伝票番号・商品コードなど
- 作業列 … 元のデータを壊さずに計算するために、空いている列に一時的に作る列。突合が終わったら消してよい
- 前処理 … 比べる前に、データの形をそろえておくこと
原因1：見た目が同じでも、Excelには違うものに見えている
目には同じでも、Excelには別物に見えるものがあります。
| 人が見て | 表1 | 表2 | = で比べると |
|---|---|---|---|
| まったく同じ | 田中太郎 | 田中太郎 | 一致 |
| 末尾に空白 | 田中太郎 | 田中太郎 | 一致しない |
| 間に空白 | 山田花子 | 山田 花子 | 一致しない |
| 英数字の全角と半角 | ABC123 | ＡＢＣ１２３ | 一致しない |
| カタカナの全角と半角 | ソフト | ｿﾌﾄ | 一致しない |
| 文字列の「1」と数値の1 | 1 | 1 | 一致しない |
| 大文字と小文字 | abc | ABC | 一致する |
最後の行だけ逆で、= は大文字と小文字を区別しません。
どれが区別されるかは直感と合わないので、目で探しても原因は見つかりません。前処理でそろえます。
使う関数によって、答えが変わる
5つの手法は、何を「同じ」と判定するかが違います。
| 人が見て | = | EXACT | COUNTIF | VLOOKUP | XLOOKUP |
|---|---|---|---|---|---|
| まったく同じ | ○ | ○ | ○ | ○ | ○ |
| 末尾に空白 | × | × | × | × | × |
| 間に空白 | × | × | × | × | × |
| 英数字の全角と半角 | × | × | × | × | ○ |
| カタカナの全角と半角 | × | × | × | × | ○ |
| 大文字と小文字 | ○ | × | ○ | ○ | ○ |
| 文字列の「1」と数値の1 | × | ○ | ○ | × | × |
① 全角と半角を同じとみなすのはXLOOKUPだけです。
「VLOOKUPで出ないのにXLOOKUPにしたら出た」の正体は、たいていこれです。
原因が直ったのではなく、たまたま拾えているだけのことがあります。
② = と EXACT は、厳しさの向きが逆です。= は大文字小文字を区別せず、数値と文字列は区別します。EXACTはその逆です。
③ COUNTIFは数値と文字列を区別しません。
「COUNTIFでは数が合うのにVLOOKUPで引けない」ときは、これが原因のことがあります。
隣のセルに =ISTEXT(A2) と入れれば見分けられます（TRUEなら文字列、FALSEなら数値）。
自分で検証するときの罠
検索範囲を表全体にして試すと、別の行の「田中太郎」などに一致して、正しい結果が出ません。
1組ずつ独立に比べ、範囲に他の候補が混ざっていないか確認してください。
原因2：COUNTIFは「無いのに1」を返す
突合の方法として「COUNTIFで数えて、0なら無い」という書き方がよく紹介されます。
手軽ですが、本当は無いものを「ある」と答えるパターンが実在します。
次の4パターンです。人が見れば全部「別物」です。
| 探した値 | 表2にあった値 | COUNTIFの答え |
|---|---|---|
1234567890123456 | 1234567890123457 | 1（あることになる） |
0001 | 1 | 1 |
AB* | ABCDEF | 1 |
SMITH? | SMITHX | 1 |
前処理をしても直りません。 前処理をかけた作業列で数えても、4つとも1のままです。
なぜこうなるのか
COUNTIFは、数字に見えるものを数値として扱います。
Excelが正確に扱える整数は15桁までなので、16桁の番号は末尾が丸められ、1桁違いが同じ値になります。0001 と 1 も、両方を数値の1として見ています。
もう1つ、COUNTIFは * を「なんでもよい」、? を「なんでもよい1文字」の意味に取ります。
コードや型番に * や ? が入っていると、別のものを拾います。
では何を使えばいいのか
同じ4パターンを、他の手法に当てるとこうなります。期待する答えは4つとも「無い」です。
| 手法 | 上の4パターンでどうなるか |
|---|---|
| COUNTIF / COUNTIFS | 4つとも誤って「ある」と答える |
| VLOOKUP / MATCH | 16桁と 0001 は正しく「無い」。* と ? で誤る |
| XLOOKUP | 4つとも正しく「無い」 |
| SUMPRODUCT＋EXACT | 4つとも正しく「無い」 |
XLOOKUP（Excel 2021以降か Microsoft 365）が使えるならXLOOKUP、それ以前なら SUMPRODUCT＋EXACT です。
式は冒頭の手順にあります。
後者は EXACT が1つ1つを文字のまま比べ、SUMPRODUCT が一致した件数を数えます（0なら1件も無い）。
行数が多いと重くなるので、鈍いと感じたらXLOOKUPが使える環境で作業するほうが現実的です。
2つを併用するなら、前処理に UPPER を必ず入れてください
XLOOKUPは大文字と小文字を区別せず、EXACTは区別します。UPPER 無しだと abc と ABC で、同じブックの中で答えが割れます。
前処理の式に UPPER があるのはこのためです。
* や ? の罠は、一致した相手の金額を持ってくるときにも出ます。AB* で ABCDEF の金額を引こうとすると、XLOOKUPは「無し」を返しますが、VLOOKUPは金額を返します。* や ? が入りうるデータでは、VLOOKUPも信用できません。
前処理：比べる前に形をそろえる
原因1のずれは、比べる前に形をそろえれば解決します。
使う式
=UPPER(SUBSTITUTE(SUBSTITUTE(ASC(TRIM(A2))," ",""),CHAR(160),""))内側から順に、こう動きます。
| 順 | 部分 | 何をするか |
|---|---|---|
| 1 | TRIM(A2) | 前後の空白を取り、間の連続した空白を1つに詰める |
| 2 | ASC(...) | 全角の英数字・カタカナ・空白・一部の記号を半角にする |
| 3 | SUBSTITUTE(..." ","") | 残った空白を全部消す |
| 4 | SUBSTITUTE(...,CHAR(160),"") | 見えない空白を消す |
| 5 | UPPER(...) | 小文字を大文字にそろえる |
この式は、数値と文字列の型の違いも解決します。TRIM が数値を文字列に変えるためです。
原因1の③で書いた「COUNTIFでは合うのにVLOOKUPで引けない」も、この式を通せば揃います。
ただし、日付と「表示形式を付けた数値」は別です（次の節で説明します）。
TRIMは「前後だけ」ではありません
よくある説明に「TRIMは前後の空白を取る」とありますが、間の連続した空白も1つに詰めます。田中␣␣太郎（空白2つ）は 田中␣太郎 になり、空白1つはそのまま残ります。
だから、TRIMのあとに SUBSTITUTE(...," ","") が要ります。上の式がその形です。
CHAR(160) とは何か
Webページや社内システムからコピーしたデータに混ざる、「見た目は空白だが空白ではない文字」です。
半角スペース（32番）とは別の文字（160番）なので、SUBSTITUTE(A2," ","") では消えません。
日付や金額をキーにするときは、もうひと手間いる
日付セルは、画面に 2026/08/23 と見えていても中身は 46257 という通し番号です。
前処理を通しても、文字列の 2026/08/23 とは一致しません。
直し方は、TEXT で見た目どおりの文字列にしてから前処理にかけることです。
=UPPER(SUBSTITUTE(SUBSTITUTE(ASC(TRIM(TEXT(A2,"yyyy/mm/dd")))," ",""),CHAR(160),""))"yyyy/mm/dd" が、そろえたい表示の形です。両方の表に同じ書式を指定してください。
表記ゆれ（2026/8/23・2026-08-23）も、通すと 2026/08/23 にそろいます。
金額も同じです（1,234.50 と見えている数値の中身は 1234.5）。
小数点以下の桁数まで指定して、文字列にします。
=TEXT(A2,"#,##0.00")桁数を省くと、別の金額が同じものになります。TEXT(1234.5,"#,##0") も TEXT(1235.4,"#,##0") も 1,235 になります。"#,##0.00" なら別々の値になります。
金額のセルに日付の書式を当てるのも同じ事故です。TEXT(1234.5,"yyyy/mm/dd") は 1903/05/18 という無関係な日付になります。
⛔ TEXTは「キーが日付のとき」だけに使ってください
数字とハイフンだけのコード（枝番・部門コードなど）は、日付として読まれて壊れます。
| 元のキー | TEXT(…,"yyyy/mm/dd") を通すと |
|---|---|
1-2 | 2026/01/02 |
2-3-4 | 2002/03/04 |
A-1 | A-1（無事） |
ABC-123 | ABC-123（無事） |
しかも補われる年は「今年」なので、来年同じ突合をすると結果まで変わります。
日付でも金額でもないキーには、TEXTを使わないでください。
ASCで直る記号・直らない記号
前処理の ASC は全角を半角にしますが、記号によって効き方が違います。ハイフンに見える文字では、こうなります。
| ASCで | 記号 |
|---|---|
| 直る | 全角ハイフン －・全角括弧 （・全角ピリオド ．（日本語入力で普通に打つ全角記号） |
| 直らない | マイナス記号 −・ハイフン ‐（U+2010）・ダッシュ —（コピー元が特殊な場合に混ざる） |
直らない記号が出たときだけ、作業列をもう1列作り、1で作った作業列の結果に対して個別に置き換えてください。
=SUBSTITUTE(Z2,"−","-")⛔ 長音符「ー」を置き換えてはいけません
長音符（カタカナの伸ばし棒）をハイフンに置き換えると、データが壊れます。
=SUBSTITUTE("スーパー","ー","-") → ス-パ-長音符は記号ではなく、カタカナの言葉の一部です。
顧客名や商品名に新しい不一致を大量に作ります。これだけは触らないでください。
前処理では直らないもの（ここから先は人間の仕事）
形をそろえても、どうにもならないものがあります。
| 表1 | 表2 |
|---|---|
株式会社ABC | (株)ABC |
株式会社ABC | ㈱ABC |
1丁目2番3号 | 1-2-3 |
山田 太郎 | 山田太郎（本人） |
| 旧姓・旧社名 | 現姓・新社名 |
どれも一致しません。文字の並びとして本当に違うからです。
「そろえる」問題ではなく「同じものだと決める」問題なので、機械には決められません。やり方は2つです。
- 表記の統一ルールを決めて、SUBSTITUTEで一括置換する（例：
(株)と㈱を株式会社に統一する）。パターンが決まっているならこれで潰せます - 一致しなかったものだけを目で確認する
たとえば1000件のうち980件が機械で一致して、残り20件を目で見る。この形が現実的です。
全件を機械で一致させようとすると、かえって時間がかかります。
一致した相手の金額や日付を持ってくる
手順2の式の「返す範囲」を、持ってきたい列に変えるだけです。
=XLOOKUP(Z2,表2!$Z$2:$Z$1000,表2!$C$2:$C$1000,"無し")表2!$C$2:$C$1000 が、持ってきたい値の入っている列です。
最後の "無し" を書かないとエラー表示（#N/A）になり、並べ替えや集計の邪魔になります。
見つからなかったものに色を付ける
色分けして見たいときは、条件付き書式を使います。
手順
表1シートのデータが入っている範囲だけを選ぶ（例：A2:A1000）。列全体を選ぶと1行ずれます- ホーム → 条件付き書式 → 新しいルール
- 「数式を使用して、書式設定するセルを決定」を選ぶ
- 数式に
=SUMPRODUCT(--EXACT(表2!$Z$2:$Z$1000,$Z2))=0と入れる - 書式ボタンで塗りつぶしの色を選び、OK
うまくいったかの確認は、表2に確実に無いデータを1件わざと作り、その行だけ色が付くか見ることです。
全部に色が付く場合は、範囲の指定か $ の位置が違っています。
この数式もEXACTを使うので、前処理に UPPER が要ります（手順1の式のままなら入っています）。
キー列に空白のセルがあるとき
キーが空の行があると、SUMPRODUCT＋EXACTの式と条件付き書式が、空どうしを「一致」と判定します。
範囲を広めに取って余った空セルでも、同じことが起きます。
手順2のXLOOKUPは空のキーに「無し」を返しますが、表2側の作業列を下まで式で埋めていると 0 が返ります。
いちばん確実なのは、突合の前に空行を消すことです。
消せない場合、古いExcel向けの式はこの形にすれば弾けます。
=IF(Z2="","(キーが空)",IF(SUMPRODUCT(--EXACT(表2!$Z$2:$Z$1000,Z2))=0,"無し","あり"))条件付き書式で空も弾きたい場合は、数式をこちらにします（範囲の余りの空行にも色が付きます）。
=OR($Z2="",SUMPRODUCT(--EXACT(表2!$Z$2:$Z$1000,$Z2))=0)よくある詰まり方
| 症状 | 原因 |
|---|---|
| 前処理したのに一致しない | 片方の表にだけ前処理をかけている。両方に同じ式を入れる |
| 日付で突き合わせると全部「無し」になる | 片方が日付、片方が文字列。TEXT() で書式を指定してから前処理にかける |
| COUNTIFでは数が合うのにVLOOKUPで引けない | 数値と文字列の型違い。前処理の式（TRIM入り）を通せば揃う |
| 一致しないはずのものが一致している | COUNTIFを使っている。XLOOKUP か SUMPRODUCT+EXACT に変える |
| 手順2と条件付き書式で答えが違う | 前処理に UPPER が入っていない（XLOOKUPは大小を区別せず、EXACTは区別するため） |
| XLOOKUPにしたら出た | 全角半角のずれだった可能性が高い。前処理を入れないと、次に別のずれが来たときにまた止まる |
| 一部だけ一致しない | 記号の種類違い（ASCで直らないもの）か、見えない空白（CHAR(160)） |
| キーが空の行が「あり」になる（古いExcel向けの式・条件付き書式） | 空どうしが一致している。空行を消すのが確実 |
| 全部一致しない | 範囲の指定違いか、比べる列を間違えている。まず1行だけ手で確認する |
関連する作業
表記ゆれをそろえたり重複を消したりする作業は、突合の前にやると後が楽です。
手順は別記事「顧客リストの名寄せをExcelだけでやる手順」にまとめています。
この記事の確認範囲
- 表と数値は Microsoft Office Home and Business 2024（日本語版）での話です。Excelのバージョンや設定によって、結果が変わることがあります
- XLOOKUP は Office 2019以前では使えません（代わりの書き方を併記しています）
- 日本語版・日本語ロケールでの話です。他の言語版では、全角半角やカナの扱いが変わることがあります
- SUMPRODUCT＋EXACTがどのくらいの行数から重くなるかは分かっていません
- 表記ゆれの種類は、記事に挙げたものがすべてではありません
コメント