本記事のポイント
「Zoom の参加ログと申込フォームを VLOOKUP で突き合わせたのに、半分が不一致で結局手作業になった」。ウェビナーやセミナーを運用する現場で、humbulls がよく聞く悩みです。
原因は関数選びではなく、突合の前に名前の表記を揃えていないことにあります。
本記事では、名前の全半角スペースを消して突合キーを作り、XLOOKUP で引き当て、COUNTIF で重複と欠損を検出する 3 式を、コピペで動く形で解説します。特別なスキルは不要で、型と巻末キットがあれば手元の 2 シートで再現できます。
完成形から見せます。下図のように、バラバラだった 3 枚のシートが、正規化した突合キーで 1 枚の統合リストにまとまります。
人が名前を目で照合する作業はゼロになり、誰が参加して誰が未参加かも同じ表の中で判定できます。
| 項目 | 内容 |
|---|---|
| 所要時間 | 30 分 (3 式を貼って列を合わせるだけ) |
| 前提条件 | Google スプレッドシート / 突合したいシートが同じブックにある |
| 追加コスト | $0 (標準関数のみ、アドオン不要) |
| 突合規模の実測 | 申込 412 件 × 参加ログ 287 行 × HubSpot エクスポートを数分で名寄せ |
Apps Script やアドオンを使う手もありますが、まずは標準関数だけで十分回ります。以下、STEP 1 から順に進めます。
「同じ人のはずなのに VLOOKUP がヒットしない」。名寄せで最初にぶつかる壁です。
申込フォームは「山田 太郎」と半角スペース、Zoom の表示名は「山田 太郎」と全角スペース、CRM は「山田太郎」とスペースなし。人間には同じでも、関数にとっては別の文字列です。
突合がずれる犯人は、ほぼ名前のスペースです。
そこで、姓と名からスペースを全部消して連結し、どのシートでも同じ形になるキー列を作ります。姓が K 列・名が L 列なら、こう書きます。
=SUBSTITUTE(SUBSTITUTE(K2," ","")," ","")&SUBSTITUTE(SUBSTITUTE(L2," ","")," ","")
内側の SUBSTITUTE(K2," ","") で半角スペースを消し、その外側でもう一度 " " (全角スペース) を消します。名の列にも同じ処理をして、& で連結します。消すのは半角と全角の 2 種類だけで、申込 412 件がどのシートでも「山田太郎」に揃います。
この正規化キー列を、突合する全シートの同じ位置に作っておきます。ここでは各シートの T 列にキーを置いた前提で進めます。
つまずきポイントは、氏名が 1 セルにまとまっている場合です。姓名が分かれていないシートは、スペース除去だけして =SUBSTITUTE(SUBSTITUTE(A2," ","")," ","") で 1 列から作れば十分です。旧姓や外字が絡む突合は、キーが揃わないぶんだけ後段の COUNTIF で拾って手当てします。
キーが揃ったら引き当てです。基準にするシート (たとえば申込リスト) のキーで、相手シートの行を丸ごと引いてきます。
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 のキー列を必ず作ってから引きます。
突合できたように見えても、キーが重複していると XLOOKUP は最初の 1 件しか返しません。同姓同名や二重申込を放置すると、静かに取りこぼします。
そこで COUNTIF でキーの出現回数を数えます。キーが T 列にあるなら、隣の列にこう置きます。
=COUNTIF($T$2:$T$500,T2)
範囲は絶対参照 ($T$2:$T$500)、条件は各行のキー (T2) にします。結果が 2 以上の行が重複です。
| 検出したいもの | 式 | 判定 |
|---|---|---|
| キーの重複 (同姓同名・二重申込) | =COUNTIF($T$2:$T$500,T2) |
2 以上なら要確認 |
| 引き当ての欠損 (未参加・名簿漏れ) | XLOOKUP の戻り値 | "-" の行が欠損 |
実案件では、412 件のうち重複が 8 件見つかりました。二重申込とテスト送信が混じっていたものです。
重複行はメールアドレスや申込日時など、名前以外の列で本人かどうかを見分けます。同姓同名が本当に別人なら、キーの末尾に社名やメールドメインを足して区別します。
つまずきポイントは範囲の取り方です。$T$2:$T$500 のように固定範囲にすると、501 行目以降が数えられません。データが伸びるシートは $T$2:$T と列全体で指定しておくと数え漏れが起きません。
名寄せができたら、そのまま HubSpot に取り込める形に整えます。突合キーは内部用なので、インポートには使いません。
HubSpot は Contacts をメールアドレスで一意に判定します。突合キーで名寄せした後、各行にメールアドレス列が揃っているかを確認し、メールをインポートの識別子に使います。
整えるのは 3 点だけです。
"-" の行は、未参加として残すか除外するかを決めてから取り込む取り込みと会社・取引への関連付けの手順は HubSpot インポートで会社・取引に関連付ける にまとめています。
つまずきポイントは、Plain Text で作ったキー列をそのまま取り込もうとすることです。突合キーは HubSpot 側に不要なので、インポート対象の列からは外します。
3 式を貼ったら、次の 3 点を確認します。
"-" になっている。#N/A が残っていない2 以上の行を目視で確認し、本人重複か別人かを判定できている3 点が揃えば、次に同じ 3 枚が来たときもキーを貼り直すだけで数分で名寄せできます。
リスト突合が手作業に戻る原因は、関数の知識ではなく名前の表記ゆれです。SUBSTITUTE で全半角スペースを消して突合キーを揃え、XLOOKUP の第 4 引数で欠損を見えるようにし、COUNTIF で重複を検出する。この 3 式をこの順に並べるだけで、申込・参加ログ・CRM エクスポートの名寄せは数分で終わります。
毎回このシートを手で組むのが面倒になったら、次は Apps Script で自動化する段です。
humbulls では、こうしたデータ整備から HubSpot 運用までを一気通貫で伴走する Growth Partner サービス を提供しています。
本文の 3 式を、手元の 2 シートの列構成に合わせて一括で生成するためのキットです。プロンプトの記入例を自社の列名に置き換えて渡してください。
種別: 実装キット 使うもの: 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 行ずつ補足
出力の確認ポイント:
"-" になっていて、#N/A のままになっていないかうまくいかないとき:
"-" になる → どちらかのシートのキー列にスペースが残っています。両シートに SUBSTITUTE のキー列を作ってから XLOOKUP を引き直してください