▲ Lookup サンプル
#N/Aだらけのリストで、VLOOKUPが沈黙するとき
システムからエクスポートしたCSV、複数人が入力した共有シート――そういったデータには、#N/A や #VALUE! が当たり前のように混じっています。そのままMATCHをかけると、エラーセルで処理が止まったり、明らかにおかしい行番号が返ったりする。
この記事で紹介する MATCH＋ISERROR の配列数式を身につければ、エラーが散在するリストを手動で目視チェックする作業は不要になります。「どこのセルがエラーか」を気にせず、ほしい値をそのまま取り出せるようになります。
なぜエラーがあるとMATCHは誤動作するのか
まず「本当に困る」状況を確認しておきましょう。以下のようなリストがあるとします。
| 行 | 商品コード（A列） | 単価（B列） |
|---|---|---|
| 2 | A-001 | 500 |
| 3 | #N/A | |
| 4 | B-002 | 300 |
| 5 | B-002 | 320 |
ここで通常のMATCH式を使うと：
=MATCH("B-002", A2:A5, 0)
結果は 3（A列の3番目＝A4セル）が返るので一見正しく見えます。しかし検索範囲の途中に #N/A が含まれているため、MATCHはそのエラー値を「何かの値」として評価しようとし、データの並びや検索値の種類によっては エラーセルを誤ってヒットさせたり、#N/A を返したりします。特に「エラーを含むセルが検索値と同じ位置にある場合」や「配列数式を絡める場合」に問題が表面化します。
問題の本質: エラーセルを「除外する」仕組みが標準のMATCHにはないため、エラーが候補に混入すると結果が信頼できなくなります。
基本の考え方：ISERROR で先にエラーを除外する
解決策は「エラーかどうかを先に判定し、エラーを除いた上で一致位置を探す」ことです。
ISERROR関数のおさらい
=ISERROR(値)
- エラーなら
TRUE - 正常値なら
FALSE
これを使って「エラーでない かつ 条件に一致する」という複合条件を組み立てます。
実践：エラーを除外して最初の一致位置を取得する
数式（配列数式）
=MATCH(1, (ISERROR(A2:A10)=FALSE)*(A2:A10="B-002"), 0)
Excel 2019以前では Ctrl+Shift+Enter で確定してください。数式バーに {=...} と波カッコが付けば配列数式として機能しています。
動作の仕組み
| 評価ステップ | 内容 |
|---|---|
ISERROR(A2:A10)=FALSE |
エラーでないセルを 1、エラーセルを 0 にする |
A2:A10="B-002" |
検索値と一致するセルを 1、不一致を 0 にする |
*（掛け算） |
両方 1 のセルだけが 1、それ以外はすべて 0 |
MATCH(1, ..., 0) |
最初に 1 が現れる位置（行番号）を返す |
検索値を TRUE ではなく 1 にしているのは、掛け算の結果が数値（1 or 0）だからです。TRUE*TRUE=1 の性質を利用して「両方の条件を満たす = 1」を探すという構造になっています。
応用1：INDEXと組み合わせて値を直接取得する
数式
=INDEX(B2:B10, MATCH(1, (ISERROR(A2:A10)=FALSE)*(A2:A10="B-002"), 0))
実行例
| 商品コード（A） | 単価（B） |
|---|---|
| A-001 | 500 |
| #N/A | （エラー）← スキップされる |
| B-002 | 300 ← これを取得 |
| B-002 | 320 |
上の数式は正しく 300 を返します。エラーセルが途中にあっても、その行は掛け算で 0 になるため完全に無視されます。
応用2：部分一致でエラーも除外する（ワイルドカード）
「コードに"B-"が含まれる最初の行」のように部分一致で検索したい場合は SEARCH 関数を組み合わせます。
=MATCH(1,
(ISERROR(A2:A10)=FALSE) *
(ISERROR(SEARCH("B-", A2:A10))=FALSE),
0)
ポイント: SEARCH は検索文字列が見つからないと #VALUE! を返します。たとえば A-001 に対して SEARCH("B-", "A-001") は #VALUE! です。そのため ISERROR(SEARCH(...))=FALSE でもう一段ラップし、「SEARCHがエラーを返さない＝部分一致した」セルだけを 1 にします。
Excel 365 / 2021 での書き方：FILTERを使う
新しいExcelでは FILTER関数 を使うと同じ処理をより直感的に書けます。
=INDEX(FILTER(B2:B10, (ISERROR(A2:A10)=FALSE)*(A2:A10="B-002")), 1)
配列数式の {=...} 記法が不要で、「条件に合う行だけ抽出してから取り出す」という意図がそのまま読めるのがFILTERの利点です。ただし古いExcelとの互換性が必要な場面では前述の配列数式が安全です。
よくあるミスと対処法
ミス1：Ctrl+Shift+Enterを忘れる
通常のEnterで確定すると先頭セルしか評価されず、ほぼ常に誤った結果が返ります。{=...} の波カッコが表示されているか必ず確認してください。
ミス2：範囲の行数がずれている
A2:A10 と B2:B10 の行数が食い違うと、意図しないセルを参照します。範囲は必ず揃えましょう。
ミス3：ISERRORの代わりにIFERRORを使ってしまう
名前が似ているので混同しがちですが、役割がまったく異なります。IFERROR は「エラーなら別の値に置き換える」関数です。誤って IFERROR(A2:A10, 0)="B-002" のように書くと、エラーセルを 0 に変換してしまい、除外どころか別の値として評価されてしまいます。エラーを「判定」するには ISERROR を使います。
まとめ
| やりたいこと | 使う関数の組み合わせ |
|---|---|
| エラーを除外して最初の一致位置を取得 | MATCH ＋ ISERROR（配列数式） |
| 値まで直接取得 | INDEX ＋ MATCH ＋ ISERROR |
| 部分一致でエラーも除外 | MATCH ＋ ISERROR ＋ SEARCH |
| 新Excel環境で簡潔に書く | FILTER ＋ ISERROR |
MATCH＋ISERROR が特に力を発揮するのは、「システムエクスポートのCSVに欠損行が混じる場面」や「複数人が入力したリストに#N/Aが散在する場面」です。データが完璧に整っていることはむしろ稀で、現場のExcelはエラーとの戦いでもあります。この数式を一度覚えると、エラー行を目で探して削除するという非効率な作業から解放されます。ぜひ手元のデータで試してみてください。
関連記事
- 【Lookupシリーズ】「〜で始まらない」最初の値をINDEX+MATCH+LEFTで一発取得する方法
- 月切り替えが一瞬！シート名をセルで動的に変えるVLOOKUP＋INDIRECTの使い方
- 【Excel実践】VLOOKUPで仕入れ・サービスコストの転記ミスをなくす方法
- Excelで"近い値"を自動検索する完全ガイド｜XLOOKUP・FILTER・ABSの実践パターン
- Excelでバージョン管理ファイルの最新版を自動取得する方法｜LOOKUP+FIND応用テク【Lookupシリーズ】
