Excel の XLOOKUP で「見た目は同じなのに一致しない」ときは、隠れた空白や印刷できない文字が混ざっていることがよくあります。結論から言うと、TRIM だけで直らない場合は CHAR(160) を SUBSTITUTE で置き換え、必要に応じて CLEAN を組み合わせたうえで、検索値と検索範囲の両方を同じルールで整形すると解決しやすくなります。XLOOKUP は既定で完全一致なので、1 文字でも中身が違えばヒットしません。 (Microsoft Support)
この記事では、XLOOKUP で隠れた空白が原因で一致しないときの最短の直し方、TRIM で消えない空白の正体、毎月の照合作業でも壊れにくい運用までまとめて整理します。すぐ直したい人向けの式と、実務向けの判断基準を分けて見ていきましょう。
結論：まず試すべき XLOOKUP の式
同じ「空白対策」でも、途中の空白を残したい列と、空白を消してよい列では使う式が変わります。ここを間違えると、空白は消せても別の不一致を増やします。
名称・部署名・商品名のように、途中の空白を残したい場合
=XLOOKUP(
TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(E2,CHAR(160)," ")," "," "))),
TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2:A1000,CHAR(160)," ")," "," "))),
B2:B1000,
"見つからない"
)この式は、Web 由来で混ざりやすい改行しないスペース（160）や全角スペースを半角スペースに寄せてから、CLEAN と TRIM で整えます。単語の区切りとしての空白は 1 つ残したい列に向いています。TRIM は余分な空白を削除しつつ、単語間の空白は 1 つ残す動きなので、名称系の列ではこちらが扱いやすいことが多いです。 (Microsoft サポート)
社員番号・型番・商品コードのように、空白を無視してよい場合
=XLOOKUP(
CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,CHAR(160),"")," ","")," ","")),
CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A1000,CHAR(160),"")," ","")," ","")),
B2:B1000,
"見つからない"
)ID やコードのように、空白そのものが意味を持たない列なら、最初から削除して照合した方が確実です。AB 123 と AB123 を同じものとして扱いたいときはこちらです。逆に、空白を含むこと自体に意味があるコード体系なら使わないでください。
TRIM は ASCII の半角スペース向け、CLEAN は 0〜31 の印刷できない文字向けで、どちらも万能ではありません。Microsoft も、不要文字の除去では TRIM、CLEAN、SUBSTITUTE の組み合わせを案内しています。 (Microsoft サポート)
なぜ XLOOKUP は見た目が同じでも一致しないのか
XLOOKUP は既定で完全一致です。つまり、画面上では同じに見えても、実際には末尾に別種の空白が 1 文字入っていたり、改行やタブが紛れていたりすると一致しません。 (Microsoft サポート)
| 混ざる文字 | よくある発生源 | TRIM 単体 | CLEAN 単体 | 実務での対処 |
|---|---|---|---|---|
| 半角スペース（32） | 手入力、CSV | ○ | × | TRIM で整理 |
| 改行しないスペース（160） | Web コピペ、HTML 由来 | × | × | SUBSTITUTE(...,CHAR(160)," ") を入れてから TRIM |
| 改行・タブなど（0〜31） | 外部システム、CSV | × | ○ | CLEAN を使う |
| CLEAN でも消えない印刷できない文字 | 一部のインポートデータ | × | × | SUBSTITUTE で個別置換 |
Microsoft も、余分な空白や印刷できない文字は並べ替え、フィルター、検索で予期しない結果を起こすと案内しています。特に TRIM は ASCII 32 の空白向けなので、日本語データで全角スペースが紛れるケースでは、別途 SUBSTITUTE を入れておく方が安全です。 (Microsoft サポート)
まずは空白が原因かを 3 分で切り分ける
いきなり大きな式に置き換えるより、原因を先に絞る方が早いです。以下では、A2 がマスタ側のキー、E2 が検索値だとします。
| 確認したいこと | 式 | 見方 |
|---|---|---|
| 先頭・末尾の空白を見たい | ="["&A2&"]" | 角括弧の内側にズレが見えたら空白あり |
| 通常の半角空白で変化するか | =A2=TRIM(A2) | FALSE なら ASCII 32 の空白の影響 |
CHAR(160) が混ざっているか | =LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(160),"")) | 1 以上なら改行しないスペースあり |
| 制御文字が混ざっているか | =LEN(A2)<>LEN(CLEAN(A2)) | TRUE なら改行・タブなどの可能性 |
| 長さがそもそも違うか | =LEN(A2)&" / "&LEN(E2) | 見た目が同じでも長さが違えば別文字列 |
TRIM でも変わらず、CLEAN でも変わらないのに一致しないなら、最優先で疑うべきは CHAR(160) です。Web や HTML を経由したデータではここが盲点になりやすいです。 (Microsoft サポート)
実務では補助列で正規化するのが最も安全
1 回だけなら XLOOKUP の中で整形してもよいのですが、実務では補助列で正規化してから照合する方が安定します。理由は単純で、どこまで整形したかが見えやすく、再計算も追いやすく、原因調査もしやすいからです。
具体的な手順
たとえば、A 列がマスタの検索キー、B 列が返したい値、E2 が検索したい値だとします。
まず、マスタ側に「整形済みキー」を作ります。
C2
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," ")," "," ")))検索側にも同じルールで整形したセルを作ります。
F2
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(E2,CHAR(160)," ")," "," ")))そのうえで、XLOOKUP は整形済み同士で照合します。
G2
=XLOOKUP(F2,$C$2:$C$1000,$B$2:$B$1000,"見つからない")コード列なら、ここで使う整形式を「空白を消す版」に変えるだけです。検索値だけでなく、マスタ側も同じ式で整形するのが重要です。片側だけ掃除しても、もう片側にゴミ文字が残っていれば一致しません。
正規化後の重複チェックも忘れない
空白を削除した結果、別のキー同士が同じ値に潰れることがあります。たとえば A 01 と A01 をどちらも A01 にしてしまうケースです。この状態で XLOOKUP を使うと、最初に見つかった一致が返ります。 (Microsoft サポート)
補助列を作ったら、重複も確認してください。
=COUNTIF($C$2:$C$1000,C2)この結果が 2 以上なら、正規化ルールが強すぎる可能性があります。名称列なら「内部の空白は残す」、コード列なら「空白は削除する」など、列の意味に合わせてルールを分けるのがコツです。
状況別のおすすめ対処法
| 状況 | おすすめ | 理由 | 注意点 |
|---|---|---|---|
| 今すぐ 1 件だけ直したい | XLOOKUP の中で整形 | 数式 1 本で対処できる | 範囲を広く取りすぎると重くなりやすい |
| 毎月・毎週同じ表を照合する | 補助列で正規化 | 原因が見やすく、再利用しやすい | 式のコピー漏れを防ぐ |
| 共有先に古い Excel がある | 補助列 + INDEX/MATCH または VLOOKUP | 互換性を確保しやすい | VLOOKUP は左検索不可、完全一致なら FALSE 必須 |
| 原因調査だけしたい | XLOOKUP のワイルドカード一致 | 候補を拾いやすい | 本番運用にそのまま使うと誤一致しやすい |
XLOOKUP は Excel 2016 / 2019 では使えません。古い環境では、補助列で整形したうえで INDEX/MATCH か VLOOKUP に切り替えるのが現実的です。なお、VLOOKUP は第 4 引数を省略すると近似一致が既定なので、照合用途では FALSE を明示してください。XLOOKUP にはワイルドカード一致もありますが、これは候補探索向けです。 (Microsoft サポート)
ワイルドカードで「とりあえず候補を見る」なら、たとえば次のように書けます。
=XLOOKUP("*"&E2&"*",A2:A1000,B2:B1000,"見つからない",2)ただし、ABC-1 を探したかったのに ABC-10 に当たるような誤一致が起きやすいので、最終形としてはおすすめしません。 (Microsoft サポート)
よくある失敗と注意点
- 検索値だけ整形して、マスタ側をそのままにしている
- 名称列なのに空白を全部削除してしまい、別表記に変えている
A:AやC:Cのように列全体を毎回整形して、シートを重くしている- 正規化後の重複キーを確認していない
- 空白ではなく、数値と文字列の型違いが本当の原因なのに見落としている
TRIM は ASCII 32 向け、CLEAN は 0〜31 向けで、どちらも万能ではありません。空白対策で改善しない場合は、数値が文字列として保存されていないかも確認してください。インポート後のデータでは別原因として起きやすく、数字キーの照合では VALUE で数値化した補助列を作る方が早いこともあります。 (Microsoft サポート)
迷ったらこの順で対応する
[と]で囲んで表示し、LEN、TRIM、CLEANで原因を切り分けるCHAR(160)を疑い、SUBSTITUTEを加える- 単発なら XLOOKUP の中で整形する
- 継続運用なら補助列で正規化する
- 最後に、正規化後の重複と数値・文字列の型違いを確認する
まずは、検索値とマスタ側のキーに同じ正規化式を 1 列ずつ作り、その列同士で XLOOKUP するところから始めてください。これで解決するなら、次にやるべきことは数式を増やすことではなく、元データの取り込み方や入力ルールを整えることです。隠れた空白を毎回その場で直すより、最初から混ぜない運用の方が長く効きます。
コメント