VLOOKUPで2番目・3番目を取得する方法｜COUNTIF補助列で5分解決
月次レポートで同じ担当者の売上明細を複数行取り出したいのに、VLOOKUPは最初の1件しか返してくれない――そんな経験はありませんか？
実はこの問題、VBAも配列数式も不要です。COUNTIFで補助列を作るだけで、2番目・3番目の一致をスッキリ取得できます。この記事では手順をステップごとに丁寧に解説します。関数を使い始めたばかりの方でも、5分あれば実践できます。
なぜVLOOKUPは1番目しか返さないのか
VLOOKUPには「検索値が複数存在しても、一番上の行の値だけを返す」という仕様があります。
たとえば以下のデータで =VLOOKUP("田中", A:B, 2, FALSE) を使っても、返るのは常に「りんご」だけです。
| A列（氏名） | B列（商品） |
|---|---|
| 田中 | りんご |
| 田中 | みかん |
| 田中 | ぶどう |
| 佐藤 | バナナ |
「みかん」や「ぶどう」を取得するには、検索値を一意（ユニーク）にする工夫が必要です。
解決策：補助列でキーを一意にする
今回使う列構成の全体像
まず完成形を確認しておきましょう。元データのA列・B列はそのままに、C列に補助列を追加します。
| A列（氏名） | B列（商品） | C列（補助列） |
|---|---|---|
| 田中 | りんご | 田中1 |
| 田中 | みかん | 田中2 |
| 田中 | ぶどう | 田中3 |
| 佐藤 | バナナ | 佐藤1 |
VLOOKUPはC列を検索列として使い、B列の値を返します。この列構成を前提に以降の手順を進めてください。
ステップ1：補助列（C列）に連番付きキーを作る
C2セルに以下の数式を入力し、データの末尾までコピーします。
=A2&COUNTIF($A$2:A2, A2)
数式のポイント：
$A$2:A2は「行が増えるにつれて自動で範囲が広がる」書き方です。C2では$A$2:A2（1行）、C3では$A$2:A3（2行）となり、同じ名前が登場した回数を正確にカウントします。A2 & COUNTIF(...)で文字列と数値を結合し、「田中1」「田中2」のようなユニークキーを生成します。
これで各行に重複しないキーが付き、VLOOKUPで「何番目」かを指定できるようになります。
ステップ2：VLOOKUPで2番目・3番目を取得する
補助列が完成したら、以下のVLOOKUP式で目的の値を取得します。
=VLOOKUP("田中"&2, C:D, 2, FALSE)
列指定の内訳：
- 検索範囲 C:D：C列（補助列）が左端、D列（商品）が右側 ※B列ではなくD列になる点に注意
- 列番号 2：範囲の2列目＝D列（商品）を返す
- 検索値 "田中"&2：「田中2」というユニークキーを検索
⚠️ よくある間違い：
C:Bのように左右が逆の範囲指定はエラーになります。VLOOKUPは必ず検索列が範囲の左端になるよう指定してください。
返り値：みかん（田中の2番目の商品）
ステップ3：N番目をセル参照にして動的に切り替える
N番目の数値をセルに入力できるようにすると、プルダウンなどで切り替えられて便利です。
=IFERROR(VLOOKUP("田中"&E2, C:D, 2, FALSE), "")
E2セルに「1」「2」「3」を入力するだけで結果が切り替わります。IFERROR で包むことで、存在しない番号を指定したときにエラーではなく空白を表示できます。
実用例：売上データから担当者別に複数明細を抽出する
ここまでの内容を実務に応用してみましょう。
データシートの構成
| A列（担当者） | B列（売上金額） | C列（補助列） |
|---|---|---|
| 山田 | 50,000 | 山田1 |
| 鈴木 | 30,000 | 鈴木1 |
| 山田 | 80,000 | 山田2 |
| 山田 | 20,000 | 山田3 |
抽出シートの設計
別シートに以下のような表を作り、数式を横・縦にコピーします。
| 担当者（A列） | 1件目（B列） | 2件目（C列） | 3件目（D列） |
|---|---|---|---|
| 山田 | ※数式 | ←コピー | ←コピー |
| 鈴木 | ←コピー | ←コピー | ←コピー |
B2セルに入力する数式：
=IFERROR(VLOOKUP($A2&B$1, データ!$C:$D, 2, FALSE), "")
参照のポイント：
- $A2：担当者名（列を固定、行は可変）
- B$1：件数「1」「2」「3」（行を固定、列は可変）
- この2つを掛け合わせることで、数式を横・縦にコピーするだけで全担当者の明細が自動展開されます。
この方法の注意点と対処法
| 注意点 | 対処法 |
|---|---|
| 補助列が目に入り見た目が気になる | 列を非表示（右クリック→「非表示」）にしてすっきりさせる |
| データ追加時に補助列の数式を伸ばす必要がある | テーブル機能（Ctrl+T）を使うと数式が自動で拡張される |
| 大量データで動作が重くなることがある | Excel 365ならXLOOKUPやFILTER関数の利用を検討する |
まとめ
- VLOOKUPは標準では1番目の一致しか返さない
- COUNTIFで連番を付けた補助列を作り、「田中1」「田中2」のようなユニークキーを生成することでN番目を取得できる
- VLOOKUPの検索範囲は補助列が左端になるよう指定する（
C:Dのように） IFERRORと組み合わせてエラー処理を忘れずに- N番目の数値をセル参照にすれば動的に切り替え可能
VBAも難解な配列数式も使わず、補助列を1列追加するだけで実現できるシンプルな方法です。一度仕組みを作ってしまえば、あとはコピーするだけで使い回せます。ぜひ今日の業務データで試してみてください！
