この記事を読むとできること
「VLOOKUPは使えるけど、もう一歩先に進みたい」という方向けの記事です。
同じ条件に一致するデータが複数行あるとき、2件目・3件目と順番に取り出す数式をステップごとに解説します。コピペで使える完成形の数式と、実務で必ず役立つエラー対策もセットで紹介します。
「VLOOKUPでは足りない」場面、ありませんか？
Excelで検索系の関数を使っていると、こんな壁にぶつかることがあります。
「担当者：田中の売上データが3件あるのに、VLOOKUPは最初の1件しか返してくれない…」
VLOOKUPは「条件に一致する最初の1件」しか取得できない仕様です。2件目・3件目が必要な場面では別のアプローチが必要になります。
この問題を解決するのが、INDEX・SMALL・IFを組み合わせた配列数式です。
💡 Excel 365 / 2021以降をお使いの方へ：
FILTER関数を使うとより簡単に同じ結果が得られます。本記事の末尾で紹介していますので、まずそちらを確認するのがおすすめです。旧バージョン（Excel 2016・2019など）をお使いの方は、このまま読み進めてください。
サンプルデータの確認
以下のような売上リストを例に解説します。
| 行 | A列（担当者） | B列（売上金額） |
|---|---|---|
| 2 | 田中 | 100,000 |
| 3 | 鈴木 | 80,000 |
| 4 | 田中 | 150,000 |
| 5 | 佐藤 | 90,000 |
| 6 | 田中 | 120,000 |
| 7 | 鈴木 | 60,000 |
目標：「田中」の売上金額を1件目・2件目・3件目と順番に取り出す
E2セルに取り出したい順番（1・2・3）を入力し、対応する売上金額をF2セルに表示させます。
完成形の数式（コピペで使えます）
最初に完成形を示します。これが今回使う正式な数式です。
=IFERROR(INDEX($B$2:$B$7, SMALL(IF($A$2:$A$7=$D$1, ROW($A$2:$A$7)-ROW($A$2)+1, 9999), E2)), "該当なし")
⚠️ 配列数式です。入力後は
Ctrl + Shift + Enterで確定してください。Excel 365では通常のEnterでも動作します。
各部分の役割を順番に解説します。
数式の仕組みをステップごとに解説
ステップ1：IF関数で「一致する行の番号」だけを取り出す
IF($A$2:$A$7=$D$1, ROW($A$2:$A$7)-ROW($A$2)+1, 9999)
A2〜A7の各セルをD1セルの値（「田中」）と比較します。
- 一致する場合 → その行の相対番号（1〜6）を返す
- 一致しない場合 →
9999（大きな数値）を返す
ROW($A$2:$A$7)-ROW($A$2)+1 は「範囲内の相対番号」を求める定番テクニックです。シート全体の行番号だと範囲の先頭が2から始まってしまうため、先頭行を引いて+1することで1始まりの番号に揃えます。
結果はこのような配列になります。
{1, 9999, 3, 9999, 5, 9999}
田中が存在するのは相対1行目・3行目・5行目であることがわかります。一致しない行は9999という大きな値に置き換えられています。
なぜ9999が必要？ IFの偽値を省略するとFALSEが返り、SMALL関数がFALSEを
0として扱うため、意図しない行番号が選ばれてしまいます。9999のように範囲外の大きな値を入れることで、SMALL関数が必ず一致した行番号を優先して選ぶようになります。
ステップ2：SMALL関数でN番目の行番号を選ぶ
SMALL({1, 9999, 3, 9999, 5, 9999}, E2)
SMALL関数は「配列の中でN番目に小さい値」を返します。9999は必ず後回しになるため、一致した行番号だけが順番に選ばれます。
| E2の値 | SMALL関数の結果 | 意味 |
|---|---|---|
| 1 | 1 | 田中1件目の相対行番号 |
| 2 | 3 | 田中2件目の相対行番号 |
| 3 | 5 | 田中3件目の相対行番号 |
ステップ3：INDEX関数で値を取り出す
INDEX($B$2:$B$7, 3)
最後にINDEX関数で「B2:B7の3行目」を取得します。B2:B7の3行目はB4セル（150,000）なので、これが「田中の2件目の売上金額」として返ってきます。
ステップ4：IFERRORでエラーを処理する
田中のデータは3件しかないのに、E2に4を入力してしまうと、SMALL関数が取り出せる値を見つけられず#NUM!エラーになります。
IFERROR(..., "該当なし")
IFERRORでラップすることで、件数を超えた場合は「該当なし」と表示され、表がきれいに保たれます。実務で使う際は必ず付けておくことをおすすめします。
実際の出力イメージ
D1セルに「田中」と入力し、E列に順番（1・2・3）、F列に数式を入れると以下のようになります。
| D列（検索条件） | E列（順番） | F列（売上金額） |
|---|---|---|
| 田中 | 1 | 100,000 |
| 田中 | 2 | 150,000 |
| 田中 | 3 | 120,000 |
| 田中 | 4 | 該当なし |
D1セルの名前を「鈴木」に変えるだけで、鈴木の売上一覧に切り替わります。
Excel 365ならFILTER関数で一発解決
Excel 365 / 2021以降をお使いなら、FILTER関数を使うと数式が大幅にシンプルになります。
=FILTER($B$2:$B$7, $A$2:$A$7=D1, "該当なし")
この1行で「田中」に一致する売上金額をすべて縦に自動展開して表示してくれます。配列数式の入力も不要です。
ただし旧バージョン（Excel 2016・2019）やGoogle Sheetsの一部ではFILTER関数が使えません。その場合は今回のINDEX・SMALL・IFが依然として強力な選択肢です。
まとめ
今回の数式で使った関数の役割を整理します。
| 関数 | 役割 |
|---|---|
| IF | 条件に一致する行の相対番号を抽出（不一致は9999） |
| SMALL | N番目に小さい行番号を選択 |
| INDEX | 指定した行番号の値を取得 |
| IFERROR | 件数オーバー時の#NUM!エラーを「該当なし」に変換 |
この記事のポイント
- VLOOKUPは最初の1件しか取れない→INDEX・SMALL・IFで解決
- IFの偽値には
9999を入れてFALSE問題を回避する IFERRORを必ず付けて実務での誤表示を防ぐ- 条件をセル参照（D1）にすることで検索対象を動的に切り替えられる
- Excel 365ならFILTER関数がさらにシンプルで便利
一見複雑な数式ですが、3つの関数それぞれの役割さえ理解すれば、さまざまなシーンに応用できる汎用テクニックになります。ぜひ手元のデータで試してみてください！
【Lookupシリーズ】では、ExcelやGoogle SheetsのLookup系関数を実践的に紹介しています。他の記事もあわせてご覧ください。
