doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

「含む」で検索したい！Excel部分一致検索で最初のヒットを取得する3つの方法

Excel 関数 部分一致 業務効率化 スプレッドシート

スポンサーリンク

「含む」で検索したい！Excel部分一致検索で最初のヒットを取得する3つの方法

「顧客リストから『株式会社』を含む会社名だけ拾いたい」「商品名に特定ブランドが入っているものを素早く見つけたい」――こんな場面、業務でよく遭遇しませんか？

完全一致のVLOOKUPは使えても、部分一致（"含む"検索）となると途端に手が止まってしまう方は多いはず。この記事では、部分一致で最初に一致するセルの値を取得する方法を3パターンで解説します。まず自分に合った方法を以下のガイドで確認してから読み進めてください。

🔍 早見ガイド：どの方法を使えばいい？

あなたの環境・状況 おすすめの方法
Excel 365 / 2021 を使っている 方法①（最もシンプル）
Excel 2019以前を使っている 方法②（配列数式で対応）
複数条件で絞り込みたい 方法③（AGGREGATE活用）

前提：サンプルデータと今回の目標

以下のデータをA1:B6に用意した想定で解説します。

A列（商品コード） B列（商品名）
A-001 りんごジュース
B-002 みかんゼリー
A-003 りんごタルト
C-004 ぶどうジャム
A-005 りんごパイ

目標：B列の中から「りんご」を含む最初の商品名をC2セルに取得する

方法①：XLOOKUP ＋ ISNUMBER ＋ SEARCH（Excel 365 / 2021推奨）

数式

C2セルに入力：

=XLOOKUP(TRUE, ISNUMBER(SEARCH("りんご", B2:B6)), B2:B6)

結果：「りんごジュース」

各関数の役割

関数 何をしているか
SEARCH("りんご", B2:B6) B2〜B6の各セルで「りんご」が何文字目にあるか数値で返す。見つからない場合はエラー
ISNUMBER(...) 数値ならTRUE、エラーならFALSEに変換し、{TRUE; FALSE; TRUE; FALSE; TRUE}という配列を作る
XLOOKUP(TRUE, ..., B2:B6) 配列の中で最初にTRUEが現れた位置に対応するB列の値を返す

XLOOKUPは配列をそのまま受け取れるため、余分な処理が不要でとてもシンプルに書けます。SEARCHは大文字・小文字を区別しないので英数字混じりの検索にも対応。区別したい場合はFINDに置き換えてください。

応用：商品コード（A列）を返したい場合

=XLOOKUP(TRUE, ISNUMBER(SEARCH("りんご", B2:B6)), A2:A6)

最後の引数（返す配列）をA列に変えるだけです。

方法②：INDEX ＋ MATCH ＋ ISNUMBER ＋ SEARCH（Excel 2019以前対応）

XLOOKUPが使えない環境では、INDEX＋MATCHの組み合わせで同じ結果を得られます。

数式

C2セルに入力後、Ctrl＋Shift＋Enterで確定：

=INDEX(B2:B6, MATCH(TRUE, ISNUMBER(SEARCH("りんご", B2:B6)), 0))

⚠️ Excel 2019以前では必ずCtrl＋Shift＋Enterで入力してください。 数式バーに{=INDEX(...)} のように{}が付けば配列数式として正しく認識されています。Excel 365では通常のEnterでOKです。

処理の流れ

ISNUMBER(SEARCH("りんご", B2:B6))
  → {TRUE; FALSE; TRUE; FALSE; TRUE}

MATCH(TRUE, {TRUE; FALSE; TRUE; FALSE; TRUE}, 0)
  → 1（最初にTRUEが現れるのは1番目）

INDEX(B2:B6, 1)
  → "りんごジュース"

ステップごとに分解すると、それぞれの関数が何をしているかが明確です。MATCHの第3引数0は「完全一致」を意味し、ここではTRUEと完全に一致する最初の位置を探します。

方法③：INDEX ＋ AGGREGATE（複数条件への拡張に強い）

AGGREGATEはあまり知られていませんが、エラーやFALSEを無視しながら条件を満たす行番号を取り出すという処理が得意です。複数条件に拡張したいときに特に威力を発揮します。

数式

C2セルに入力（通常のEnterでOK）：

=INDEX(B2:B6, AGGREGATE(15, 6, IF(ISNUMBER(SEARCH("りんご", B2:B6)), ROW(B2:B6)-ROW(B2)+1), 1))

各引数の意味を丁寧に解説

まずIF部分の処理：

IF(ISNUMBER(SEARCH("りんご", B2:B6)), ROW(B2:B6)-ROW(B2)+1)
  • ISNUMBER(SEARCH(...)) → {TRUE; FALSE; TRUE; FALSE; TRUE}
  • TRUEの場合：ROW(B2:B6)-ROW(B2)+1 で相対行番号を算出 → {1; FALSE; 3; FALSE; 5}
  • FALSEの場合：そのままFALSEを返す（AGGREGATEで無視される）

次にAGGREGATEの引数：

引数 値 意味
第1引数 15 SMALL関数として動作（小さい順に値を返す）
第2引数 6 エラーとFALSEを無視するオプション
第3引数 IF(...) 処理対象の配列
第4引数 1 小さい方から1番目（＝最小値＝最初の一致行）を返す

結果として{1; 3; 5}の中の最小値1が返り、INDEX(B2:B6, 1)で「りんごジュース」が取得できます。

複数条件への拡張例

「りんご」を含み、かつA列が「A-」で始まる商品を探したい場合：

=INDEX(B2:B6, AGGREGATE(15, 6, IF((ISNUMBER(SEARCH("りんご", B2:B6)))*(ISNUMBER(SEARCH("A-", A2:A6))), ROW(B2:B6)-ROW(B2)+1), 1))

条件を*（AND条件）でつなぐだけで柔軟に拡張できます。

方法の比較まとめ

方法 対応バージョン シンプルさ 複数条件拡張
XLOOKUP ＋ ISNUMBER ＋ SEARCH Excel 365 / 2021 ◎ ○
INDEX ＋ MATCH ＋ ISNUMBER Excel 2013以降 ○ △
INDEX ＋ AGGREGATE Excel 2010以降 △ ◎

よくあるエラーと対処法

#N/A が返る場合

検索文字列がどのセルにも見つからないときに発生します。IFERRORでラップして代替テキストを表示しましょう。

=IFERROR(XLOOKUP(TRUE, ISNUMBER(SEARCH("りんご", B2:B6)), B2:B6), "該当なし")

大文字・小文字を区別したい場合

SEARCHをFINDに置き換えてください。たとえば「Apple」と「apple」を区別したいケースで有効です。

=XLOOKUP(TRUE, ISNUMBER(FIND("Apple", B2:B6)), B2:B6)

まとめ

  • Excel 365 / 2021ユーザー → XLOOKUP＋ISNUMBER＋SEARCH が最短・最シンプル
  • Excel 2019以前ユーザー → INDEX＋MATCH の配列数式で同等の結果が得られる
  • 複数条件で絞り込みたい場合 → AGGREGATE を使うと柔軟に拡張できる

部分一致検索は一度マスターすれば、顧客管理・商品管理・ログ分析など幅広い業務で活きるスキルです。今回紹介した数式をそのままコピーして、ご自身のデータで試してみてください！