▲ Lookup サンプル
はじめに：否定条件の検索、VLOOKUPでは対応できない
Excelや Google Sheetsで「商品コードが"T"で始まらない最初の品番を取り出したい」という場面、実務でよくあります。
たとえば、取引先別にコードが振られた商品リストから、自社扱い以外（"T"で始まる他社品番を除く）の最初のエントリを素早く特定したいケースです。
VLOOKUPや単純なMATCHのワイルドカード（*や?）は「〜を含む」「〜で始まる」には使えますが、否定条件（〜で始まらない）には使えません。
この記事を読めば、INDEX + MATCH + LEFT を組み合わせた数式1つで、否定条件のLookupができるようになります。
基本の考え方
数式の基本構造
=INDEX(返したい列, MATCH(TRUE, LEFT(検索列, 文字数)<>"プレフィックス", 0))
この数式は配列数式（複数のセルをまとめて一括計算する特殊な入力方法）を使って動作します。処理の流れは次のとおりです。
LEFT(検索列, 文字数)— 各セルの先頭N文字を取り出す<>"プレフィックス"— 指定文字列と等しくないかどうかをTRUE/FALSEの配列に変換MATCH(TRUE, ..., 0)— 最初にTRUEになる行番号を見つけるINDEX— その行番号に対応する値を返す
シンプルに言えば「先頭文字が違う最初の行はどこか？」を配列で全件チェックして返す仕組みです。
実践例①：検索列の値をそのまま返す
データ例
| A列（商品コード） | B列（商品名） |
|---|---|
| T-001 | Tシャツ |
| T-002 | タオル |
| H-010 | 帽子 |
| T-003 | タンクトップ |
| S-001 | スニーカー |
A列の商品コードが「T」で始まらない最初のコードを取り出します。
=INDEX(A2:A6, MATCH(TRUE, LEFT(A2:A6, 1)<>"T", 0))
Excelの場合：通常のEnterではなく Ctrl + Shift + Enter で配列数式として確定します。数式バーに {=...} と波括弧が付けば成功です。
Google Sheetsの場合：ARRAYFORMULA を使います。
=INDEX(A2:A6, MATCH(TRUE, ARRAYFORMULA(LEFT(A2:A6,1)<>"T"), 0))
どちらか使いやすい方で試せば十分です。
結果：H-010 が返ります。
実践例②：検索列と返す列が異なる場合（実務で最頻出）
実務では「A列のコードで条件判定して、B列の商品名を返したい」というパターンがほとんどです。INDEX の範囲をB列に変えるだけで対応できます。
=INDEX(B2:B6, MATCH(TRUE, LEFT(A2:A6, 1)<>"T", 0))
MATCH 側の検索条件はA列のまま据え置き、INDEX でB列を参照する点がポイントです。
結果：帽子 が返ります。
実践例③：複数文字のプレフィックスに対応する
「旧_」（2文字）で始まらない最初の商品名を探す場合は、LEFT の第2引数をプレフィックスの文字数に合わせます。
=INDEX(B2:B10, MATCH(TRUE, LEFT(A2:A10, 2)<>"旧_", 0))
（※配列数式として入力。Excelは Ctrl + Shift + Enter）
応用編：2番目・3番目の一致も取得する
条件を満たす2番目・3番目の値も連続して取得したい場合は、SMALL + IF + ROW を組み合わせます。
=INDEX(A$2:A$10, SMALL(IF(LEFT(A$2:A$10,1)<>"T", ROW(A$2:A$10)-ROW(A$2)+1), 1))
SMALL(..., 1)→ 最初の一致SMALL(..., 2)→ 2番目の一致SMALL(..., 3)→ 3番目の一致
基本パターンより難易度が上がるため、まず実践例①②で動きを確認してから試してください。
よくあるエラーと対処法
#N/A が返る
MATCHが一致する値を見つけられない場合に発生します。
- 検索範囲に該当する値が本当に存在するか確認
- 配列数式として正しく入力されているか（波括弧
{}があるか）確認 IFERRORでラップして「見つからない」を明示する
=IFERROR(INDEX(A2:A10, MATCH(TRUE, LEFT(A2:A10,1)<>"T", 0)), "該当なし")
注意：大文字・小文字の扱い
LEFTはデフォルトで大文字・小文字を区別しません（"T" と "t" は別物として扱われます）。大文字小文字を無視して比較したい場合は、LOWERで統一してから比較します。=INDEX(A2:A10, MATCH(TRUE, LOWER(LEFT(A2:A10,1))<>"t", 0))この点は見落としやすいので、思った結果が返らないときは真っ先に確認してください。
まとめ
| やりたいこと | 使う関数 |
|---|---|
| 〜で始まらない最初の値 | INDEX + MATCH + LEFT + 否定（<>） |
| 検索列と異なる列の値を返す | INDEX の範囲を別列に変えるだけ |
| N番目の一致を取得 | INDEX + SMALL + IF + ROW |
「始まらない」否定条件のポイントは、LEFT で先頭文字を切り出して不等号（<>）で比較することです。配列数式として入力することで、セル1つの数式で全行を動的にチェックできます。
実務のデータ整理や集計作業にぜひ活用してみてください。
このシリーズでは、INDEX・MATCH・VLOOKUPなどのLookup系関数を使った実践的なテクニックを順次紹介しています。
