はじめに：フルーツかくれんぼ！見つからないのは誰？
普段、VLOOKUPやXLOOKUPを使って、「このリストにあるデータを探して！」という処理はよく行いますよね。
では、逆はどうでしょう？
「リストには載っているのに、データ範囲の中には一度も登場しないデータを探して！」
いわゆる「未入力チェック」や「欠番探し」です。
「ある」を探すのは簡単ですが、「ない」を探すとなると、途端に頭を抱えてしまう方も多いのではないでしょうか。
今回のテーマは、「逆転の発想！『ある』ではなく『ない』データを検索する方法」です。
大量の果物データの中から、上手にかくれんぼしている（入力されていない）「ある果物」を、王道の関数から超マニアックな関数パズルまで、8種類のアプローチで特定します。
このテクニックを身につければ、データの不整合チェックや、入力漏れの確認作業が劇的に楽になりますよ！
本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！
準備：果物の山とチェックリスト
まずは、今回の舞台となるデータを用意しましょう。
1. 果物の山 (A1:H8)
8×8のマス目に、様々な果物の名前がランダムに入力されています。
以下の数式を任意のセルに入力します。
=INDEX({"リンゴ";"ミカン";"バナナ";"モモ";"メロン";"ブドウ";"マンゴー"},RANDARRAY(8,8,1,7,1))
2. チェックリスト (B11:B18)
今回の確認対象となる、8種類の果物リストです。
={"リンゴ";"ミカン";"バナナ";"モモ";"メロン";"イチゴ";"ブドウ";"マンゴー"}
このリストの中に1つだけ、果物の山（A1:H8）に一度も登場していない果物が隠れています。（正解は「イチゴ」です！）
さあ、数式の力でこの「隠れている果物」を見つけ出しましょう！
第1段階：「ひとつ」を探せ！（ないデータが1つの場合）
まずは、欠けているデータが「1つだけ」と分かっている場合の捜索メソッドを6つ紹介します。
アプローチ1, 2, 3：COUNTIFを使った「0」探しトリオ
考えかた
最も基本的かつ強力なアプローチです。
「データ範囲の中に、その果物が何個あるか？」を数えればいいのです。
もし個数が「0」なら、それが隠れているデータです！
数式と解説
以下の3つの数式は、どれも「個数が0のものを見つける」という同じロジックで動いています。
1. 王道！XLOOKUP
=XLOOKUP(0,COUNTIF(A1:H8,B11:B18),B11:B18)
2. 古いExcelでもOK！LOOKUP
=LOOKUP(2,1/(COUNTIF(A1:H8,B11:B18)=0),B11:B18)
3. 実は最強？FILTER
=FILTER(B11:B18,COUNTIF(A1:H8,B11:B18)=0)
【共通ロジックの解説】
1. COUNTIF(A1:H8,B11:B18)
この部分で、B11:B18の各果物がA1:H8の中に何個あるか一気に数えます。
結果は {11; 4; 9; 13; 7; 0; 11; 9} のような配列になります。
「イチゴ」の場所だけ「0」になっていますね！
2. あとは、この配列から「0」を探すだけ！
・XLOOKUP: 0を検索値にして、対応する果物名を返します。
・LOOKUP: 1/(...=0) でエラーを発生させ、唯一計算できる「イチゴ」の場所を特定する古典テクニックです。
・FILTER: 個数が0である行だけをフィルタリングして表示します。実はこれが、結果が2つ以上あっても対応できる一番潰しが利く方法です。
LOOKUP関数については、「LOOKUP関数の使い方！IFより簡単な条件分岐」にて詳しく解説しています！
アプローチ4：現在でも最強！INDEX & MATCH & TOCOL
考えかた
COUNTIFを使わず、検索関数だけで挑む方法です。
「リストの果物を、データ範囲から探してみる」→「見つからない（エラーになる）やつが正解だ！」という逆転の発想です。
数式と解説
=INDEX(B11:B18,MATCH(1,ISNA(MATCH(B11:B18,TOCOL(A1:H8),0))*1,0))
1. TOCOL(A1:H8): まず、8×8の表を縦一列のデータに変換し、検索しやすくします。
TOCOL関数以外の方法で縦一列に変換する方法は、Excel 複数列のデータを縦一列にする8つの方法を参照ください。
2. MATCH(B11:B18, ..., 0): リストの各果物をデータ内で探します。見つかれば数値（位置）、見つからなければ #N/A エラーになります。
3. ISNA(...): ここがポイント！エラー（見つからない）ならTRUE、見つかったらFALSEになります。
つまり「イチゴ」だけがTRUEになります。
4. MATCH(1, ...*1, 0): TRUE(1)になっている場所を探し、その位置を特定します。
5. INDEX(...): 最後に、その位置にある果物名を取り出します。
アプローチ5：完全なる関数パズル（並べ替えと欠番探し）
考えかた
これは、「データ範囲にある果物」をリストアップして並べ替え、「本来のリスト」と突き合わせて、「あれ？順番通りならここにあるはずの果物が違うぞ？」というズレ（欠番）を見つける、非常に高度なパズルです。
数式と解説（前半）
=OFFSET(B10,MATCH(FALSE,IFERROR(EXACT(SORT(UNIQUE(TOCOL(MATCH(A1:H8,B11:B18,0)))),SEQUENCE(COUNTA(B11:B18))),FALSE),0),0)
どうですか、この数式！
MATCH(FALSE,IFERROR(EXACT(SORT(UNIQUE(TOCOL(MATCH(...
なんだかたくさんの関数が整然と並んでいて、呪文みたいでカッコよくないですか？（笑）
実用性は限りなくゼロに近いですが、「関数を組み合わせてパズルを解く」というExcelの奥深い楽しさが詰まっています！
【超・詳細解説】
1. MATCH(A1:H8,B11:B18,0): データ範囲の果物を、リストの「何番目か（インデックス番号）」に変換します。
2. SORT(UNIQUE(TOCOL(...))): その番号を縦一列にし、重複を消して、昇順に並べます。
もし「イチゴ（リストの6番目）」が欠けていれば、{1; 2; 3; 4; 5; 7; 8} のように「6」が抜けた配列になります。
3. SEQUENCE(COUNTA(B11:B18)): 本来あるべき連番 {1; 2; 3; 4; 5; 6; 7; 8} を作ります。
4. EXACT(..., ...): 手順2（実データ）と手順3（理想データ）を比較します。
1=1, 2=2… と進みますが、6番目で「実データは7」「理想は6」となり、ここで不一致（FALSE）が発生します！
数式と解説（後半）
★ここが最重要ポイント：IFERROR(…, FALSE)
もし、欠けているのが途中の「イチゴ（6番）」ではなく、リストの最後の「マンゴー（8番）」だったらどうなるでしょう？
実データは {1;...;7}（7個）、理想は {1;...;8}（8個）となり、配列の長さが合いません。
この時、Excelは足りない部分を比較しようとして #N/A エラーを出します。
そこで IFERROR を使い、このエラーを FALSE（不一致） に変換することで、「最後のデータが足りない場合」も正しく「不一致」として検出できるようにしているのです。
5. MATCH(FALSE, ..., 0): 最初の「FALSE（不一致）」が起きた場所（今回は6番目）を特定します。
6. OFFSET(...): リストの見出し（B10）から6つ下のセルを参照し、「イチゴ」を特定します。
アプローチ6：MMULT（行列計算で消去法）
考えかた
リストの果物一つひとつに対して、「データ範囲の中に存在するか？」を総当たりでチェックし、存在した果物の名前を消してしまう（空白にする）方法です。
MMULT関数を使い倒したい方は、Excel MMULT関数で合計・順位・平均を出す7つの方法もご覧ください！
数式と解説
=CONCAT(IF(MMULT((TOROW(A1:H8)=B11:B18)*1,SEQUENCE(COUNTA(A1:H8))^0),"",B11:B18))
1. (TOROW(A1:H8)=B11:B18): データ範囲（横一列化）とリストを比較し、巨大なTrue/Falseの表を作ります。
2. MMULT(...): これを行列計算で集計すると、「各果物がデータ範囲に何個あったか」という配列ができます。
3. IF(..., "", B11:B18): 個数が1以上（存在した）なら「””（空文字）」に、0（存在しなかった）なら「元の名前（イチゴ）」にします。
4. CONCAT(...): 最後にそれらを連結します。存在した果物はすべて空文字になって消えるため、残った「イチゴ」だけが表示されます。
第2段階：「複数」隠れている場合（ないデータが2つ以上）
次は難易度が上がります。
リストには載っているのに、現場（データ範囲）にない果物が「2つ以上」ある場合です。
（例として、B19に「アボカド」を追加し、それもデータ範囲にはない状態を想定します）
アプローチ7：やっぱり最強 FILTER
考えかた
アプローチ3で紹介したFILTER関数は、答えが複数あっても、そのままスピル（全表示）してくれます。
数式と解説
=FILTER(B11:B19,COUNTIF(A1:H8,B11:B19)=0)
解説：
解説不要なほどシンプルですね！「個数が0個である果物」をすべて抽出します。
イチゴとアボカドが両方とも表示されます。実務ではこれが間違いなく最強の解です。
アプローチ8：FILTER禁止！？ 古典的配列数式
考えかた
もしFILTER関数が使えない古いExcelで、複数の結果を表示させたい場合はどうすればいいでしょう？
「該当する行番号を全部リストアップして、小さい順にINDEXで取り出す」という、往年の配列テクニックを使います。
この挙動に関しては、Excel FILTER関数の代替案3選！関数パズルに挑戦にて詳しく解説しています！
数式と解説
=LET(a,IF(COUNTIF(A1:H8,B11:B19)=0,ROW(B11:B19)-ROW(B10)),IFNA(INDEX(B11:B19,SMALL(a,ROW(INDIRECT("A1:A"&COUNT(a))))),""))
解説：
この数式では、ロジックを分かりやすく整理するために、最新のLET関数を使っています。
また、最後にエラー処理としてIFNA関数（Excel 2013以降）を使っていますが、これをIFERROR関数に置き換えれば、Excel 2007以降でも動作する古典的な配列数式になります。古き良き時代の「配列数式」の知恵ですね。
1. IF(COUNTIF...=0, ROW(...)-ROW(...)): 個数が0の果物について、「リストの何番目か」という番号を計算します。該当しない果物はFALSEになります。
例：{FALSE; FALSE; ...; 6; FALSE; ...; 9} （6番目のイチゴと9番目のアボカド）
2. SMALL(a, ...): この配列から、1番小さい数字（6）、2番目に小さい数字（9）…と順番に取り出します。
3. INDEX(...): 取り出した番号を使って、リストから名前を取得します。
昔はこうやって、非常に長い数式を書いて「複数検索」を実現していたのです。今はFILTER一発。いい時代になりましたね！
まとめ：ないものを探すなら、FILTERが最強！
「ある」を探すのと違い、「ない」を探すロジックには、COUNTIFで0を探したり、ISNAでエラーを探したりと、一捻りした面白さがありましたね。
結論としては、FILTER関数が圧倒的に便利で強力です。
単一でも複数でも、数式を変えることなく対応できる柔軟性は、現代Excelの宝刀と言えるでしょう。
しかし、アプローチ5の「並べ替えと欠番探し」のようなパズル的思考や、アプローチ8の「古いExcelでの意地」も、知っておくとExcelの理解が深まります。
「データがない！」と焦る前に、これらの数式でクールに隠れた果物（欠損データ）を見つけ出してみてくださいね！
