スプレッドシートの名寄せ関数 — 3 式でリスト突合を自動化

本記事のポイント

• リスト突合でつまずく原因は関数の知識ではなく、名前のスペースがバラバラなまま照合していることです。SUBSTITUTE で全半角スペースを消して突合キーを揃えるだけで、VLOOKUP のずれはほぼ消えます
• 突合キー作り (SUBSTITUTE) → 引き当て (XLOOKUP の第 4 引数) → 品質チェック (COUNTIF) の 3 式をこの順で並べれば、申込フォーム・参加ログ・CRM エクスポートの名寄せがコピペで再現できます
• ウェビナー後に 3 枚のシート (申込 412 件 / 参加ログ 287 行 / HubSpot エクスポート) を突き合わせる作業を、目視で半日かけていたところから数分に短縮できます

「Zoom の参加ログと申込フォームを VLOOKUP で突き合わせたのに、半分が不一致で結局手作業になった」。ウェビナーやセミナーを運用する現場で、humbulls がよく聞く悩みです。

原因は関数選びではなく、突合の前に名前の表記を揃えていないことにあります。

本記事では、名前の全半角スペースを消して突合キーを作り、XLOOKUP で引き当て、COUNTIF で重複と欠損を検出する 3 式を、コピペで動く形で解説します。特別なスキルは不要で、型と巻末キットがあれば手元の 2 シートで再現できます。

できあがるもの — 3 枚のリストが 1 枚に名寄せされる

完成形から見せます。下図のように、バラバラだった 3 枚のシートが、正規化した突合キーで 1 枚の統合リストにまとまります。

人が名前を目で照合する作業はゼロになり、誰が参加して誰が未参加かも同じ表の中で判定できます。

Apps Script やアドオンを使う手もありますが、まずは標準関数だけで十分回ります。以下、STEP 1 から順に進めます。

STEP 1. 突合キーを作る — SUBSTITUTE で全半角スペースを消す

「同じ人のはずなのに VLOOKUP がヒットしない」。名寄せで最初にぶつかる壁です。

申込フォームは「山田 太郎」と半角スペース、Zoom の表示名は「山田　太郎」と全角スペース、CRM は「山田太郎」とスペースなし。人間には同じでも、関数にとっては別の文字列です。

突合がずれる犯人は、ほぼ名前のスペースです。

そこで、姓と名からスペースを全部消して連結し、どのシートでも同じ形になるキー列を作ります。姓が K 列・名が L 列なら、こう書きます。

=SUBSTITUTE(SUBSTITUTE(K2," ",""),"　","")&SUBSTITUTE(SUBSTITUTE(L2," ",""),"　","")

内側の SUBSTITUTE(K2," ","") で半角スペースを消し、その外側でもう一度 "　" (全角スペース) を消します。名の列にも同じ処理をして、& で連結します。消すのは半角と全角の 2 種類だけで、申込 412 件がどのシートでも「山田太郎」に揃います。

この正規化キー列を、突合する全シートの同じ位置に作っておきます。ここでは各シートの T 列にキーを置いた前提で進めます。

つまずきポイントは、氏名が 1 セルにまとまっている場合です。姓名が分かれていないシートは、スペース除去だけして =SUBSTITUTE(SUBSTITUTE(A2," ",""),"　","") で 1 列から作れば十分です。旧姓や外字が絡む突合は、キーが揃わないぶんだけ後段の COUNTIF で拾って手当てします。

STEP 2. XLOOKUP で引き当てる — 第 4 引数で欠損を可視化する

キーが揃ったら引き当てです。基準にするシート (たとえば申込リスト) のキーで、相手シートの行を丸ごと引いてきます。

VLOOKUP ではなく XLOOKUP を使います。列番号を数えずに済み、見つからないときの戻り値を自分で指定できるからです。申込リストの T 列キーで、フォーム回答シートの A〜S 列を引くならこう書きます。

=XLOOKUP(T2,'フォームの回答 1'!$T:$T,'フォームの回答 1'!$A:$S,"-")

第 1 引数が探すキー、第 2 引数が相手シートのキー列、第 3 引数が返したい範囲です。

効いてくるのは第 4 引数の "-" です。

第 4 引数 (missing_value) は、一致が見つからなかったときに返す値です。ここを "-" にしておくと、標準の #N/A でセルが埋まらず、未参加や名簿漏れの行が「-」として一目で分かります。

287 行の Zoom 参加ログ側のキーで同じ XLOOKUP をもう 1 本引けば、「申込あり・参加あり」「申込あり・参加なし (-)」がひとつの表に並びます。この 2 本で、フォローアップの出し分けがそのまま作れます。

つまずきポイントは、第 2 引数と第 3 引数の行数がずれるケースです。キー列は $T:$T、返す範囲は $A:$S のように列全体で揃えると、行の増減があってもずれません。相手シートの名前が全角スペース混じりのままだと当然ヒットしないので、相手シート側にも STEP 1 のキー列を必ず作ってから引きます。

STEP 3. 重複と欠損を検出する — COUNTIF で品質を確かめる

突合できたように見えても、キーが重複していると XLOOKUP は最初の 1 件しか返しません。同姓同名や二重申込を放置すると、静かに取りこぼします。

そこで COUNTIF でキーの出現回数を数えます。キーが T 列にあるなら、隣の列にこう置きます。

=COUNTIF($T$2:$T$500,T2)

範囲は絶対参照 ($T$2:$T$500)、条件は各行のキー (T2) にします。結果が 2 以上の行が重複です。

実案件では、412 件のうち重複が 8 件見つかりました。二重申込とテスト送信が混じっていたものです。

重複行はメールアドレスや申込日時など、名前以外の列で本人かどうかを見分けます。同姓同名が本当に別人なら、キーの末尾に社名やメールドメインを足して区別します。

つまずきポイントは範囲の取り方です。$T$2:$T$500 のように固定範囲にすると、501 行目以降が数えられません。データが伸びるシートは $T$2:$T と列全体で指定しておくと数え漏れが起きません。

STEP 4. HubSpot インポート用に整える

名寄せができたら、そのまま HubSpot に取り込める形に整えます。突合キーは内部用なので、インポートには使いません。

HubSpot は Contacts をメールアドレスで一意に判定します。突合キーで名寄せした後、各行にメールアドレス列が揃っているかを確認し、メールをインポートの識別子に使います。

整えるのは 3 点だけです。

• 統合リストから、HubSpot に入れたい列 (メール / 氏名 / 参加状況 / 申込日) だけを別シートにコピーする
• XLOOKUP が返した "-" の行は、未参加として残すか除外するかを決めてから取り込む
• 参加状況のような区分は、後で workflow で使えるよう表記を統一しておく (「参加」「未参加」など)

取り込みと会社・取引への関連付けの手順は HubSpot インポートで会社・取引に関連付ける にまとめています。

つまずきポイントは、Plain Text で作ったキー列をそのまま取り込もうとすることです。突合キーは HubSpot 側に不要なので、インポート対象の列からは外します。

動作確認 — 何が見えれば成功か

3 式を貼ったら、次の 3 点を確認します。

• キー列: 全シートの T 列が「山田太郎」のようにスペースなしで揃っている。全角スペースが残った行がない
• XLOOKUP 列: 一致した行は相手シートの値が入り、未一致の行が "-" になっている。#N/A が残っていない
• COUNTIF 列: 2 以上の行を目視で確認し、本人重複か別人かを判定できている

3 点が揃えば、次に同じ 3 枚が来たときもキーを貼り直すだけで数分で名寄せできます。

まとめ — キーを揃えれば、突合は関数で終わる

リスト突合が手作業に戻る原因は、関数の知識ではなく名前の表記ゆれです。SUBSTITUTE で全半角スペースを消して突合キーを揃え、XLOOKUP の第 4 引数で欠損を見えるようにし、COUNTIF で重複を検出する。この 3 式をこの順に並べるだけで、申込・参加ログ・CRM エクスポートの名寄せは数分で終わります。

毎回このシートを手で組むのが面倒になったら、次は Apps Script で自動化する段です。

• 関数運用をスクリプトに移す: HubSpot を Apps Script で Sheets に同期する
• ウェビナー運用全体の流れ: Zoom ウェビナー × Google フォームの運用

humbulls では、こうしたデータ整備から HubSpot 運用までを一気通貫で伴走する Growth Partner サービス を提供しています。

🤖 AI 実行キット

本文の 3 式を、手元の 2 シートの列構成に合わせて一括で生成するためのキットです。プロンプトの記入例を自社の列名に置き換えて渡してください。

キット① 手元の 2 シートに合う名寄せ数式一式を作る — 15 分

種別: 実装キット 使うもの: Claude (ブラウザ版で可) 事前に用意するもの: 突合したい 2 枚のシートそれぞれの列構成 (何列目に姓・名・メールがあるか) と、どちらを基準に引き当てたいか

プロンプト:

Google スプレッドシートで 2 枚のリストを名寄せする数式一式を作ってください。

【私のシート構成】
- シートA (基準): 「申込リスト」。姓=K列 / 名=L列 / メール=C列（記入例。自社の列に置き換え）
- シートB (引き当て先): 「フォームの回答 1」。姓=D列 / 名=E列 / 返したい範囲=A〜S列
- 突合キーを置く列: 各シートの T 列

【必ず守る作り方（本文の型を転写）】
1. 突合キーは SUBSTITUTE を二重に使い、半角スペース " " と全角スペース "　" を両方消して姓+名を & で連結する
2. 引き当ては XLOOKUP を使い、第 4 引数（missing_value）を "-" にして未一致を可視化する
3. 重複チェックは COUNTIF で各キーの出現回数を数え、2 以上を要確認とする。範囲は列全体で指定する

【出力】
- シートA・シートBそれぞれの T 列に入れる SUBSTITUTE 数式
- シートAの各行に入れる XLOOKUP 数式
- 重複チェック用の COUNTIF 数式
- それぞれ何行目に貼るか、コピーはどの範囲かを 1 行ずつ補足

出力の確認ポイント:

• SUBSTITUTE が半角と全角の両方を消しているか (片方だけだとキーが揃いません)
• XLOOKUP の第 4 引数が "-" になっていて、#N/A のままになっていないか
• COUNTIF の範囲が列全体か、少なくともデータ末尾までを含んでいるか

うまくいかないとき:

• 一致するはずの行が "-" になる → どちらかのシートのキー列にスペースが残っています。両シートに SUBSTITUTE のキー列を作ってから XLOOKUP を引き直してください
• 同姓同名が 1 件にまとまってしまう → キーの末尾にメールドメインや社名を足して区別する数式を追記するよう指示してください

参考文献

• SUBSTITUTE — Google ドキュメント エディタ ヘルプ (取得日: 2026-07)
• XLOOKUP function — Google ドキュメント エディタ ヘルプ、第 4 引数 missing_value の仕様 (取得日: 2026-07)
• COUNTIF function — Google ドキュメント エディタ ヘルプ (取得日: 2026-07)