月次レポートの作成中、「担当者ごと・商品ごとに全件リストアップしたいのに、VLOOKUPだと最初の1件しか拾えない…」と手が止まった経験はありませんか？
条件が複数になった瞬間に、Excelの数式が途端に難しく感じられるのはよくあることです。この記事では、SMALL・INDEX・IFの組み合わせを使って「複数条件に一致するすべての値」を一覧で取り出す方法を、ステップバイステップで解説します。
まず確認：あなたのExcelバージョンはどれ？
解法は使っているExcelのバージョンによって大きく変わります。最初に自分に合ったルートを選んでください。
| バージョン | おすすめの方法 |
|---|---|
| Excel 365 / 2021 | FILTER関数（1行で完結・最もシンプル） |
| Excel 2019以前 | SMALL + INDEX + IF（この記事のメイン解説） |
Excel 365 / 2021をお使いの場合、以下の1行で全件抽出できます。
=FILTER(C2:C7, (A2:A7="田中")*(B2:B7="りんご"), "")
それ以外のバージョンの方は、このまま読み進めてください。
今回使うサンプルデータ
以下の売上テーブルを例に進めます。
| A列（担当者） | B列（商品） | C列（売上金額） |
|---|---|---|
| 田中 | りんご | 1000 |
| 鈴木 | みかん | 2000 |
| 田中 | みかん | 1500 |
| 田中 | りんご | 3000 |
| 鈴木 | りんご | 500 |
| 田中 | りんご | 2500 |
目標：「担当者＝田中」かつ「商品＝りんご」のすべての売上金額（1000・3000・2500）をE列に取り出す
STEP 1：複数条件を「掛け算」で表現する
ExcelのIF・SMALLなどは単体では複数条件を扱えません。そこで、条件を「掛け算（乗算）」で繋ぐことで、擬似的なAND条件を作ります。
(A2:A7="田中") * (B2:B7="りんご")
TRUEは1、FALSEは0として扱われるため、両方の条件を満たす行だけが1、どちらかでも外れれば0になります。
{1; 0; 0; 1; 0; 1} ← 1行目・4行目・6行目が一致
STEP 2：一致件数を確認する（SUMPRODUCT）
式を組む前に、何件一致しているか確認しておくと便利です。
=SUMPRODUCT((A2:A7="田中")*(B2:B7="りんご"))
結果は 3 です（1行目・4行目・6行目が該当）。
この件数が、後でSMALLに渡す「最大取得数」の目安になります。
STEP 3：一致する行番号を順番に取り出す（SMALL + IF）
ここがこの解法の核心です。条件に一致する行の「相対行番号」を小さい順に取り出します。
{=SMALL(IF((A$2:A$7="田中")*(B$2:B$7="りんご"), ROW(A$2:A$7)-ROW(A$2)+1), ROW(A1))}
⚠️
{}は手入力しません。数式を入力後、Ctrl + Shift + Enter で確定すると自動で付きます。
式の各パーツを理解する
| パーツ | 役割 |
|---|---|
(A$2:A$7="田中")*(B$2:B$7="りんご") |
両条件を満たす行を1、それ以外を0に変換 |
ROW(A$2:A$7)-ROW(A$2)+1 |
データ範囲内の相対行番号（1〜6）を生成する |
IF(条件, 相対行番号) |
条件を満たす行番号だけを残し、外れた行はFALSE（無視）にする |
SMALL(..., ROW(A1)) |
残った行番号のうち「小さい順にN番目」を取得する |
ROW(A$2:A$7)-ROW(A$2)+1 がなぜ必要か
ROW(A$2:A$7) はシートの絶対行番号（2〜7）を返します。そのままだとINDEXに渡したときにズレるため、-ROW(A$2)+1 を引くことで「データ範囲の中での1始まりの連番（1〜6）」に変換しています。
STEP 4：行番号から実際の値を取り出す（INDEX）
STEP 3で得た行番号を使って、C列の売上金額を取り出します。
{=IFERROR(INDEX(C$2:C$7, SMALL(IF((A$2:A$7="田中")*(B$2:B$7="りんご"), ROW(A$2:A$7)-ROW(A$2)+1), ROW(A1))), "")}
IFERROR(..., "") を最初から組み込むのがポイントです。一致件数（3件）を超えた4行目以降で発生する #NUM! エラーを空白に置き換えられます。
入力手順
- E2セルに上の数式を入力し、Ctrl + Shift + Enter で確定
- E2セルを選択し、E4セルまで下にコピー（一致件数＋余裕を持たせた行数分）
結果
| E列（抽出結果） |
|---|
| 1000 |
| 3000 |
| 2500 |
田中さんのりんごの売上3件が、すべて正しく取り出せました。
応用：合計だけが欲しい場合はSUMPRODUCT
全件リストではなく「合計値だけ知りたい」という場合は、SUMPRODUCTが最もシンプルです。Ctrl + Shift + Enter 不要で使えます。
=SUMPRODUCT((A2:A7="田中")*(B2:B7="りんご")*C2:C7)
結果：6500（1000 + 3000 + 2500）
まとめ：解法の選び方と関数の役割
| やりたいこと | 使う関数・方法 |
|---|---|
| 全件リスト（365/2021） | FILTER |
| 全件リスト（2019以前） | IFERROR + INDEX + SMALL + IF（配列数式） |
| 件数カウント | SUMPRODUCT |
| 合計値だけ | SUMPRODUCT × 金額列 |
SMALL・INDEX・IFの組み合わせは、初見では複雑に見えます。しかし「条件で行番号を絞り込み → 小さい順に取り出し → INDEXで値を引く」という3段階のロジックを一度体で覚えると、様々な集計・抽出シーンに応用できます。
まずはこの記事のサンプルデータをそのまま使って、手を動かしてみてください。数式の動きが視覚的につかめると、自分のデータへの応用がぐっと楽になります。
次回は「XLOOKUPで複数条件を扱う方法」を解説予定です。お楽しみに！
