Excel で顧客リストや申込データを照合するとき、「このメールアドレスは基準リストに存在するのか？」を一瞬で判定できると、とても効率が上がります。本記事では、Sheet1 の A2 が Sheet2 の列 H に存在するかを判定する実用的な数式と、応用テクニックまで丁寧に解説します。
シナリオ整理：Sheet1 の A2 が Sheet2 の列 H にあるか確認したい
まずは今回の前提を整理します。
- Sheet1 の A2 には、判定したい メールアドレス が入っている。
- Sheet2 の列 H（
Sheet2!H:H）には、基準となるメールアドレス一覧 が入っている。 - Sheet1 で、「存在するなら Yes」「存在しないなら No」 を返したい。
よくある構成例としては、以下のようなイメージです。
| シート名 | 列 | 内容 |
|---|---|---|
| Sheet1 | A列 | 判定したいメールアドレス（例：問い合わせフォームからの申込一覧） |
| Sheet1 | B列 | メールアドレスが Sheet2 にあれば「Yes」、なければ「No」を表示したい列 |
| Sheet2 | H列 | 基準となるメールアドレスリスト（例：既存顧客、会員リストなど） |
このとき、次のような数式を試してエラーになってしまうことがあります。
=IF(A2='Sheet2'!H:H,"Yes","No")→ Spill Range too large（スピル範囲が大きすぎます）=NOT(ISERROR(MATCH(A2,'Sheet2'!H:H,0)))→ 数式として認識されず空白=IF(ISNUMBER(MATCH(A2,'Sheet2'!H:H,0),YES,No)→ 引数が多すぎます エラー
これらの問題を一気に解決する、シンプルで汎用性の高い方法が COUNTIF 関数 + IF 関数 を使うパターンです。
結論：最もおすすめの式は COUNTIF + IF 関数
判定に使う基本形の数式はこれです。
=IF(COUNTIF(Sheet2!H:H, A2) > 0, "Yes", "No")
この式を、例えば Sheet1 の B2 に入力して、下方向にコピーして使います。
式の構造を分解して理解する
| 要素 | 意味 |
|---|---|
Sheet2!H:H | 検索対象の範囲（Sheet2 の H 列すべて） |
A2 | 探したい値（Sheet1 にあるメールアドレス） |
COUNTIF(Sheet2!H:H, A2) | A2 の値が Sheet2!H:H に何件あるかをカウント |
COUNTIF(...) > 0 | 「1件以上あれば TRUE、0件なら FALSE」 |
IF(条件, "Yes", "No") | 条件が TRUE なら Yes、FALSE なら No を返す |
つまり、
- Sheet2!H:H に A2 と同じメールアドレスが 1件でもあれば Yes
- 1件も見つからなければ No
という判定が、たった 1 行の式で実現できるわけです。
実際の入力手順（初心者向け）
- Sheet1 を開きます。
- 結果を表示したいセル（例：B2）をクリックします。
- 数式バーに次の式を入力します。
=IF(COUNTIF(Sheet2!H:H, A2) > 0, "Yes", "No") - Enter キーを押します。
- オートフィル（B2 右下の小さな四角）をダブルクリックして、下方向の A3, A4,… にもコピーします。
これで、Sheet1 の各メールアドレスが Sheet2 に存在するかどうかを、一気に判定できるようになります。
COUNTIF を使うメリット
- 構文が単純：
COUNTIF(範囲, 条件)だけでよいので覚えやすい。 - スピルエラーを避けやすい：1セルで完結した集計なので、
H:Hのような列全体を指定しても基本的に問題ありません。 - 「何件あるか」もすぐ分かる：判定だけでなく、純粋な件数が知りたいときは
COUNTIF(Sheet2!H:H, A2)だけで OK。
MATCH 関数・XMATCH 関数で同じ判定をする方法
VLOOKUP などを使い慣れている方や、Microsoft 365 の最新関数を積極的に使いたい方は、MATCH 関数や XMATCH 関数でも同じ判定 ができます。
MATCH 関数での判定
MATCH 関数を使うなら、次の書き方が正解です。
=IF(ISNUMBER(MATCH(A2, Sheet2!H:H, 0)), "Yes", "No")
ポイントは次の通りです。
| 要素 | 意味 |
|---|---|
MATCH(A2, Sheet2!H:H, 0) | A2 の値を Sheet2!H:H の中から完全一致で探し、見つかった位置（行番号に相当する番号）を返す。 見つからないと #N/A エラーになる。 |
ISNUMBER( ... ) | MATCH の結果が数値（= 見つかった）なら TRUE、エラーなら FALSE |
IF(ISNUMBER(...), "Yes", "No") | TRUE のとき Yes、FALSE のとき No を返す |
よくある間違いとして、次のように IF のカッコの位置を誤ってしまうケースがあります。
=IF(ISNUMBER(MATCH(A2, Sheet2!H:H, 0), "Yes", "No"))
この書き方だと、ISNUMBER のカッコの中に「Yes」「No」まで入ってしまい、IF 関数としては「引数が多すぎます」エラーになります。
必ず、
ISNUMBER(MATCH(...))を先に閉じてから- IF の第 2 引数以降に
"Yes"と"No"を書く
という順番を守ってください。
Microsoft 365 なら XMATCH 関数も便利
Microsoft 365 や Excel 2021 以降で利用できる XMATCH 関数 でも、ほぼ同じ書き方で判定できます。
=IF(ISNUMBER(XMATCH(A2, Sheet2!H:H, 0)), "Yes", "No")
XMATCH は MATCH の強化版で、検索方法（前方検索・後方検索など）の指定が柔軟ですが、今回のような「完全一致で存在確認」だけならどちらを使っても結果は同じです。
なぜ最初の式はエラーになってしまったのか
はじめに挙げた、うまくいかなかった式の理由を整理しておくと、今後のトラブルシューティングにも役立ちます。
=IF(A2='Sheet2'!H:H,"Yes","No") がスピルエラーになる理由
この式は、
A2='Sheet2'!H:Hの部分で、「A2 の値」と「H 列全体」を一気に比較- 結果として、TRUE/FALSE の配列（大量の結果）を返そうとする
という動作をしようとするため、古いバージョンの Excel や、スピル先のセルに他の値が入っている場合などに 「Spill range too large（スピル範囲が大きすぎる）」 というエラーになります。
1セルだけで Yes / No を返したいだけなら、このような配列比較は不要で、COUNTIF や MATCH のような「1値を返す関数」を使った方が適切です。
=NOT(ISERROR(MATCH(A2,'Sheet2'!H:H,0))) が動かない理由
式そのものは、ロジックとしては間違っていません。MATCH がエラーなら FALSE、そうでなければ TRUE を返す形です。
しかし、
- セルの先頭に
=が入っていない - 全角の括弧やカンマを使用してしまっている
- セルが「文字列」書式になっていて、数式として解釈されていない
といった場合、Excel はそれを数式として認識できず「ただの文字」とみなしてしまいます。その結果、セルには何も表示されない、もしくは文字列としてそのまま表示されることになります。
特に Web からコピペした数式を貼り付けた場合、見た目が同じでも全角記号になっていることがあるので注意が必要です。
=IF(ISNUMBER(MATCH(A2,'Sheet2'!H:H,0),YES,No) が引数エラーになる理由
この式の問題点は、
ISNUMBER(MATCH(A2,'Sheet2'!H:H,0),YES,No)と、ISNUMBER のカッコの中に Yes/No まで入ってしまっている- IF の引数の位置関係が崩れてしまっている
という構造上のミスです。
IF 関数の正しい基本形は、
=IF(条件, 条件がTRUEのときの値, 条件がFALSEのときの値)
ですので、今回のようなパターンでは必ず、
=IF(ISNUMBER(MATCH(...)), "Yes", "No")
と、ISNUMBER(MATCH(…)) を条件部分として先に閉じる必要があります。
シート名に空白がある場合の注意点
シート名に空白が含まれている場合は、必ず シングルクォートで囲む 必要があります。
例：Sheet 2 という名前のシートの場合
=IF(COUNTIF('Sheet 2'!H:H, A2) > 0, "Yes", "No")
空白だけでなく、日本語名や記号を含むシート名も、基本的にはシングルクォートで囲んでおくと安全です。
応用：複数行を一気に判定したい場合
メールアドレスが A 列に大量に並んでいる場合、基本的には「1行目に式を入れて下にコピー」すれば OK です。
例えば、Sheet1 の A2～A1000 までメールアドレスが入っているとき:
- B2 に
=IF(COUNTIF(Sheet2!H:H, A2) > 0, "Yes", "No")を入力 - B2 を選択したまま、右下のフィルハンドルをダブルクリック
- B3～B1000 まで自動的に数式がコピーされる
この方法であれば、動的配列のスピルを使わずに、一気に判定を広げることができます。
絶対参照の確認
今回の COUNTIF の式では、検索範囲 Sheet2!H:H は下にコピーしても変わりません。そのため、絶対参照（$）を付けなくても問題ありません。
もし範囲を列全体ではなく、Sheet2!H$2:H$1000 のように行の一部に指定する場合は、
=IF(COUNTIF(Sheet2!$H$2:$H$1000, A2) > 0, "Yes", "No")
のように $ で絶対参照にしておくと、コピーしても範囲がずれず安心です。
COUNTIF の限界と応用：部分一致・複数条件
部分一致で判定したい場合（ワイルドカード）
もし、「A2 の文字列を含むアドレスが H 列に 1つでもあれば Yes」といった、部分一致での判定をしたい場合は、COUNTIF のワイルドカードを使います。
=IF(COUNTIF(Sheet2!H:H, "*" & A2 & "*") > 0, "Yes", "No")
ここでの * は、「0文字以上の任意の文字列」を意味します。
複数条件（COUNTIFS）で判定したい場合
例えば、
- Sheet2 の H 列：メールアドレス
- Sheet2 の I 列：ステータス（例：有効、退会済み）
という構成で、「メールアドレスが同じで、なおかつステータスが『有効』のものが存在するかどうか」を判定したいケースなら、COUNTIFS 関数 を使うと便利です。
例：Sheet1 の A2 にメールアドレス、B2 に「有効」という文字が入っている場合
=IF(COUNTIFS(Sheet2!H:H, A2, Sheet2!I:I, "有効") > 0, "Yes", "No")
「複数の条件をすべて満たす行が 1件でもあれば Yes」という判定が簡単に実現できます。
大小文字（大文字・小文字）を区別したい場合
COUNTIF や MATCH は、基本的に 大文字・小文字を区別しません。例えば、
は同じものとして扱われます。
メールアドレスの場合、この挙動で問題ないことが多いですが、どうしても厳密に区別したい場合は、EXACT 関数 + SUMPRODUCT という少し高度なテクニックを使います。
=IF(SUMPRODUCT(--EXACT(A2, Sheet2!H:H)) > 0, "Yes", "No")
この式のポイントは以下の通りです。
EXACT(A2, Sheet2!H:H)で、A2 と H 列全体を「大文字・小文字を区別して完全一致比較」- 結果は TRUE/FALSE の配列になるので、
--で 1/0 に変換 - SUMPRODUCT で 1 の数（完全一致の件数）を合計
これにより、「大文字小文字まで含めて完全一致するメールアドレスが 1件でもあれば Yes」という厳密な判定が可能になります。
条件付き書式で「存在しないメールアドレス」を色付けする
COUNTIF を使った判定は、単に Yes/No を表示するだけでなく、条件付き書式 と組み合わせることで視覚的に分かりやすくすることもできます。
条件付き書式の設定例
目的：Sheet1 の A 列にあるメールアドレスのうち、Sheet2 の H 列に存在しないものを赤色で強調表示する。
- Sheet1 で、A2:A1000（判定したい範囲）を選択します。
- 「ホーム」タブ → 「条件付き書式」 → 「新しいルール」をクリックします。
- 「数式を使用して、書式設定するセルを決定」を選びます。
- 数式欄に次の式を入力します。
=COUNTIF(Sheet2!H:H, A2)=0 - 「書式」ボタンをクリックして、フォント色や塗りつぶし色（例：赤）を設定します。
- OK を押してルールを適用します。
これで、Sheet2 に存在しないメールアドレスだけが自動的に色付きになり、「どれが未登録か」「どれが新規か」が一目で分かるようになります。
うまく判定できないときのチェックリスト
式は合っているのに結果が変、というときは、データ側に原因があることが多いです。次のポイントを順番にチェックしてみてください。
余分な空白文字が入っていないか
見た目には同じに見えても、実際には末尾にスペースが入っているケースがあります。
"[email protected]""[email protected] "（末尾にスペース）
この 2つは Excel にとっては別の文字列なので、COUNTIF や MATCH では一致しません。
簡単な対処法として、判定用の列の横に「空白除去済み」の補助列を作り、
=TRIM(A2)
のように TRIM 関数を使って両端の空白を削除してから判定すると、精度が上がります。
全角スペース・不可視文字が紛れ込んでいないか
コピー＆ペーストで取り込んだデータには、
- 全角スペース
- 改行コード
- タブ文字
などの不可視文字が紛れ込んでいることがあります。これらも COUNTIF などには影響します。
より強力にクリーンアップしたい場合は、次のような式が有効です。
=TRIM(CLEAN(SUBSTITUTE(A2, " ", " ")))
SUBSTITUTE(A2, " ", " ")：全角スペースを半角スペースに置き換えCLEAN(...)：制御文字・不可視文字を削除TRIM(...)：前後と連続したスペースを削除
数値・日付が文字列になっていないか
今回の例はメールアドレスなので問題になりにくいですが、電話番号やIDなどの数字を判定対象にしている場合、
- 片方は「数値」
- 片方は「文字列」
という差があると、COUNTIF や MATCH では一致しません。
セルの表示形式や、左寄せ・右寄せの違い（数値は右寄せ、文字列は左寄せが標準）などを目安に、データの型をそろえるようにしましょう。
パフォーマンスとメンテナンスの観点
大量データを扱う場合、数式の書き方によってファイルの重さが変わることがあります。
列全体参照と範囲限定のバランス
Sheet2!H:H のような列全体参照は、
- 書き方がシンプルでメンテナンスしやすい
- 行が増えても式を直す必要がない
というメリットがありますが、行数が数十万行単位になると、計算に時間がかかる場合があります。
データ量が多い場合は、例えば実際のデータ範囲を見て、
Sheet2!$H$2:$H$50000
のように範囲を絞ることで、パフォーマンスを改善できることがあります。
COUNTIF と MATCH のどちらを使うべきか
今回の目的のような「存在するかどうか」だけを知りたいのであれば、
- 結果が Yes/No だけで良い → COUNTIF + IF が分かりやすい
- 見つかった位置（行番号）も使いたい → MATCH / XMATCH を使う
というように使い分けると、後のメンテナンスも楽になります。
実務でありがちなアレンジ例
Yes/No の代わりに日本語メッセージを表示する
ユーザー向けの画面では、Yes/No よりも日本語で分かりやすくしたいケースもあります。その場合は、IF の中身を変えるだけです。
=IF(COUNTIF(Sheet2!H:H, A2) > 0, "登録済み", "未登録")
さらに、
- 「既存顧客」/「新規顧客」
- 「会員」/「非会員」
など、用途に合わせて自由にラベルを変えることができます。
別シートに「未登録リスト」だけを抽出する
判定結果を利用して、「Sheet2 に存在しないメールアドレスだけを別シートにまとめたい」というニーズもよくあります。
Microsoft 365 であれば、FILTER 関数と組み合わせることで、次のような式で未登録リストを一気に抽出できます。
=FILTER(Sheet1!A2:A1000, COUNTIF(Sheet2!H:H, Sheet1!A2:A1000)=0)
このように、今回の COUNTIF の考え方は、抽出・集計など他の場面にも応用が利きます。
まとめ：Excel での「存在判定」は COUNTIF + IF が基本形
本記事では、
- Sheet1 の A2 にあるメールアドレスが、Sheet2 の H 列に存在するかどうかを判定する方法
- COUNTIF + IF 関数を使った最もシンプルで実務向きな解決策
- MATCH / XMATCH 関数を使う場合の正しい書き方と、エラーになる原因
- 部分一致、大文字小文字の区別、複数条件などの応用テクニック
- 条件付き書式や FILTER 関数との連携による実務での活用例
などを解説しました。
改めて、最もベーシックで汎用性の高い式は次の 1行です。
=IF(COUNTIF(Sheet2!H:H, A2) > 0, "Yes", "No")
この考え方さえ身につけておけば、「この値が別シートのリストに存在するか？」というチェックを、どんな場面でも素早く・正確に行えるようになります。顧客管理、会員リストの照合、申込データの重複チェックなど、Excel 実務で出てくるさまざまなシーンに活用してみてください。
コメント