Excelで部分一致する全データを一括抽出する方法【古いバージョン対応の組み合わせ技】
VLOOKUPで部分一致の全件抽出をしようとして、途中で詰まった経験はありませんか？
たとえば「Appleを含む商品名をすべてリストアップしたい」というとき、VLOOKUPやXLOOKUPでは最初の1件しか取れません。かといってFILTER関数はExcel 2019以前では使えない……。
そんなときに頼りになるのが、INDEX・AGGREGATE・ISNUMBER・SEARCHの組み合わせです。スピルなし・古いバージョン対応で、部分一致する全データを上から順に抽出できます。
この記事を読めば、どのバージョンのExcelでも部分一致の全件抽出ができるようになります。ステップごとに分解して解説するので、数式が苦手な方も安心して読み進めてください。
完成数式から全体像をつかむ
まず完成形を見ておきましょう。抽出結果を表示したいセル（例：E2）に入れる数式はこちらです。
=IFERROR(INDEX($A$2:$A$7, AGGREGATE(15, 6, IF(ISNUMBER(SEARCH($D$2,$A$2:$A$7)), ROW($A$2:$A$7)-ROW($A$2)+1, NA()), ROW(A1))), "")
複雑に見えますが、4つのパーツに分解すると意外とシンプルです。以降でステップごとに仕組みを説明します。
使うデータと完成イメージ
今回は以下のデータを使います。
| A列（商品名） |
|---|
| Apple Juice |
| Orange Soda |
| Apple Cider |
| Grape Juice |
| Pineapple Smoothie |
| Lemon Tea |
D2セルに検索キーワード apple を入力すると、E列に次の3件が上から順に抽出されるのがゴールです。
| E列（抽出結果） |
|---|
| Apple Juice |
| Apple Cider |
| Pineapple Smoothie |
「Pineapple」も抽出されるのがポイントです。SEARCH関数は大文字・小文字を区別しないため、apple で Apple も Pineapple も拾えます。
使う関数の役割
| 関数 | 役割 |
|---|---|
| SEARCH | キーワードが何文字目にあるか返す（大文字小文字不問） |
| ISNUMBER | 数値ならTRUE、エラーならFALSEに変換 |
| AGGREGATE | エラーを無視しながらk番目の行番号を取得 |
| INDEX | 行番号から実際の値を取り出す |
数式の組み立てをステップで理解する
ステップ1：SEARCH＋ISNUMBERで「含む／含まない」を判定する
=ISNUMBER(SEARCH($D$2, $A$2:$A$7))
SEARCH関数はキーワードが見つかれば位置を数値で返し、見つからなければエラーを返します。ISNUMBERはそれを受け取り、数値ならTRUE・エラーならFALSEに変換します。
A2:A7に対して実行すると、結果はこうなります。
{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}
Apple Juice・Apple Cider・Pineapple Smoothieの行がTRUEになりました。
ステップ2：TRUEの行だけ行番号を返し、それ以外はエラーにする
=IF(ISNUMBER(SEARCH($D$2, $A$2:$A$7)), ROW($A$2:$A$7)-ROW($A$2)+1, NA())
TRUEの行にはINDEX用の相対行番号（1始まり）を返し、FALSEの行はNA()でエラーにします。
{1; #N/A; 3; #N/A; 5; #N/A}
ここで空文字（""）ではなくNA()を使う理由：AGGREGATEはエラーのみを無視します。空文字や0はエラーではないため、誤って行番号として拾われてしまいます。必ずNA()を使いましょう。
ステップ3：AGGREGATEでk番目の行番号を取り出す
=AGGREGATE(15, 6, IF(..., ROW(...)-ROW($A$2)+1, NA()), ROW(A1))
15：SMALL相当の処理（小さい順に取得）6：エラーを無視するオプション- 最後の
ROW(A1)：何番目の値を取るか（1, 2, 3…と変化）
E2ではROW(A1)=1で1番目に小さい行番号（→1）、E3ではROW(A2)=2で2番目（→3）、E4では3番目（→5）が返ります。数式をそのままコピーするだけでkが自動的に増えていくのがポイントです。
ステップ4：INDEXで実際の値を取り出す
=INDEX($A$2:$A$7, AGGREGATE(...))
ステップ3で得た行番号をINDEXに渡すと、対応する商品名が返ります。最後にIFERRORで囲むと、件数を超えた行が空白になりスッキリします。
よくあるエラーと対処法
数式を入れたら全部エラーになる
原因として多いのは行番号の調整ミスです。 ROW($A$2:$A$7) だけを使うと2〜7の絶対行番号が返るため、INDEXの相対位置（1〜6）とずれてしまいます。必ず ROW($A$2:$A$7)-ROW($A$2)+1 と書いてください。
件数より多くコピーしたら#VALUEが表示される
これは正常な動作です。IFERRORでラップしていれば空白になります。まだラップしていない場合は以下を確認してください。
=IFERROR(INDEX(...), "")
大文字・小文字を区別して抽出したい
SEARCHをFINDに置き換えるだけでOKです。FINDは大文字・小文字を厳密に区別します。
次にやること
- 小さなデータで動作確認する：6行程度のリストで数式を試し、期待通りに抽出されるか確かめましょう
- データが増減するならテーブル化する：Ctrl+Tでテーブル化しておくと、データが増えても数式の範囲が自動で広がります
- Excel 365・2021ならFILTER関数も検討する：互換性が不要な環境では、よりシンプルなFILTERも有力な選択肢です
=FILTER($A$2:$A$7, ISNUMBER(SEARCH($D$2,$A$2:$A$7)), "")
古いバージョンとの共有ファイルや、スピルを使いたくない場面では今回のAGGREGATE方式が引き続き活躍します。
まとめ
INDEX・AGGREGATE・ISNUMBER・SEARCHの組み合わせを使えば、古いバージョンのExcelでも部分一致する全データを上から順に抽出できます。
数式の核心は「ISNUMBERで一致判定→NA()でエラー化→AGGREGATEでエラーを飛ばして行番号取得→INDEXで値取得」という流れです。ステップを一つずつ確認しながら組み立てると、複雑に見えた数式も怖くなくなります。
まずは手元の小さなデータで試してみて、動作を確認してから本番データに応用してみてください。
Lookup関数シリーズの他の記事もあわせてご覧ください。
