▲ Lookup サンプル
はじめに
「VLOOKUPでは"含まない"条件が指定できず、困った経験はありませんか？」
商品リストから「返品」という文字が入っていない最初の注文を探す、ログから「エラー」を含まない最初の行を抽出する――こうした場面で、直感的な関数が存在しないことに気づきます。
この記事を読めば、INDEX ・ MATCH ・ ISNUMBER ・ SEARCH を組み合わせて「含まない最初のセル」を確実に取得できるようになります。複数条件への応用やエラー対処まで、一通り使いこなせる内容を解説します。
核心テクニック：ISNUMBER + SEARCH で「含む/含まない」を判定する
まずこのペアを押さえることが、すべての出発点です。
=ISNUMBER(SEARCH("検索文字", A1))
- A1に「検索文字」が含まれる →
TRUE - A1に「検索文字」が含まれない →
FALSE
仕組みはシンプルです。SEARCH は文字が見つかれば位置番号（数値）を、見つからなければ #VALUE! エラーを返します。ISNUMBER はエラーを FALSE と判定するため、「含むかどうか」が TRUE/FALSE に変換されます。
「含まない」にするには、頭に NOT を付けるだけです。
=NOT(ISNUMBER(SEARCH("検索文字", A1)))
FIND との使い分け：
SEARCHは大文字・小文字を区別しません。「ERROR」と「error」を別物として扱いたい場合はFINDに置き換えてください。一般的な日本語テキストではSEARCHで十分です。
基本の数式：含まない最初のセルを抽出する
サンプルデータ
| 行 | A列（ステータス） | B列（金額） |
|---|---|---|
| 2 | 受注済み | 5,000 |
| 3 | 返品対応中 | 3,200 |
| 4 | 返品完了 | 1,800 |
| 5 | 受注済み | 7,400 |
| 6 | 出荷準備中 | 2,100 |
「返品を含まない最初の行の金額」を取得する数式です。
=INDEX(B2:B6, MATCH(TRUE, NOT(ISNUMBER(SEARCH("返品", A2:A6))), 0))
Excel 365・2021以降：Enter で確定するだけでOKです。
Excel 2019以前：Ctrl + Shift + Enter で確定してください。
なぜ旧バージョンでは Ctrl+Shift+Enter が必要か
通常のEnter確定では、Excelは数式をセル1つ分だけ評価します。A2:A6 のような範囲全体を一度に判定させるには、「配列数式として処理せよ」という指示が必要で、それが Ctrl + Shift + Enter です。正しく入力されると数式バーに {=...} のように波括弧が付きます。Excel 365以降はこの処理が自動化されたため、通常のEnterで動作します。
数式の仕組みを分解する
NOT(ISNUMBER(SEARCH("返品", A2:A6))) は、範囲全体に対して次の配列を生成します。
| セル | SEARCH結果 | ISNUMBER | NOT |
|---|---|---|---|
| A2「受注済み」 | #VALUE! | FALSE | TRUE |
| A3「返品対応中」 | 1 | TRUE | FALSE |
| A4「返品完了」 | 1 | TRUE | FALSE |
| A5「受注済み」 | #VALUE! | FALSE | TRUE |
| A6「出荷準備中」 | #VALUE! | FALSE | TRUE |
生成配列：{TRUE, FALSE, FALSE, TRUE, TRUE}
MATCH(TRUE, {TRUE, FALSE, FALSE, TRUE, TRUE}, 0) → 最初のTRUEの位置 1
INDEX(B2:B6, 1) → B2の値 5,000 が返ります。
応用：複数条件（AND条件）を加える
「返品を含まない、かつ金額が3,000以上の最初の行」を取得したい場合は * でAND条件をつなぎます。
=INDEX(A2:A6, MATCH(1, (NOT(ISNUMBER(SEARCH("返品", A2:A6)))) * (B2:B6 >= 3000), 0))
ここで MATCH(TRUE,...) から MATCH(1,...) に変わる理由を補足します。
* 演算子で条件を掛け合わせると、ExcelはTRUE/FALSEを数値（1/0）に変換して計算します。結果は TRUE×TRUE=1、それ以外は 0 という数値の配列になります。MATCH(TRUE,...) はブール値のTRUEを探すため、数値の 1 にはマッチしません。MATCH(1,...) に変えることで正しく機能します。
よくあるエラーと対処法
#N/A エラーが出る
条件を満たす行が1つも存在しない場合に発生します。IFERROR でラップして対処します。
=IFERROR(
INDEX(B2:B6, MATCH(TRUE, NOT(ISNUMBER(SEARCH("返品", A2:A6))), 0)),
"該当なし"
)
まとめ
| 関数 | 役割 |
|---|---|
SEARCH |
文字列の含有を位置番号で検出（エラー＝含まない） |
ISNUMBER |
位置番号をTRUE/FALSEに変換 |
NOT |
TRUE/FALSEを反転して「含まない」を表現 |
MATCH |
条件配列から最初の一致位置を取得 |
INDEX |
位置番号から実際の値を取得 |
ISNUMBER + SEARCH で「含む/含まない」をブール値に変換し、MATCH + INDEX で位置を取り出す――この2ステップの組み合わせが理解できれば、検索条件の幅が大きく広がります。複数条件では * で掛け合わせて MATCH(1,...) を使う点が、最初のつまずきポイントなので覚えておいてください。
次のステップとして、Excel 365ユーザーであれば XLOOKUP で同様の操作をよりシンプルに書く方法も試してみてください。
関連記事
- 【Lookup活用術】#N/Aが混じったデータでも確実に値を取れる MATCH＋ISERROR の使い方
- 【Lookupシリーズ】「〜で始まらない」最初の値をINDEX+MATCH+LEFTで一発取得する方法
- 月切り替えが一瞬！シート名をセルで動的に変えるVLOOKUP＋INDIRECTの使い方
- 【Excel実践】VLOOKUPで仕入れ・サービスコストの転記ミスをなくす方法
- Excelで"近い値"を自動検索する完全ガイド｜XLOOKUP・FILTER・ABSの実践パターン
