VLOOKUPで2番目以降を抽出する方法｜重複データを一括表示する手順【図解】

VLOOKUPで検索値が重複しているとき、2番目以降のデータも取得したい…そんな悩みを解決します。

VLOOKUPは仕様上、検索値に一致する最初の1件しか返しません。しかし、補助列+COUNTIF関数を組み合わせることで、2番目以降の結果も一括で抽出できます。

本記事では、VLOOKUPで複数の重複結果を一括表示する方法を、具体的な手順付きで初心者向けに解説します。

VLOOKUPで2番目以降が取得できない理由

VLOOKUPは検索範囲の上から下へ検索し、最初に一致した行のデータを返す仕様です。そのため、同じ検索値が複数ある場合でも、常に1番目のデータだけが返されます。

VLOOKUPの基本的な仕様

• =VLOOKUP(検索値, 範囲, 列番号, FALSE) の基本構文
• 検索範囲の上から順に検索 → 最初に一致した行を返す
• 2番目以降のデータは無視される

解決策の考え方

• 検索値をユニークにする（重複を回避）
• 補助列でCOUNTIFによる連番を付ける
• 連番付き検索値でVLOOKUPすれば2番目以降も取得可能

【実践】VLOOKUPで2番目以降を一括抽出する手順

ここからが本記事のメインパートです。具体的な4ステップで解説します。

STEP1: 補助列でCOUNTIFによる連番を作る

元データの横に補助列を追加し、以下の数式を入力します。

数式: =COUNTIF($A$2:A2, A2)

これにより、同じ値が何番目に出現したかの連番が自動で振られます。

STEP2: 検索値+連番の結合キーを作成する

もう1つ補助列を追加し、元の値と連番を結合します。

数式: =A2&B2

「りんご」が3つあれば「りんご1」「りんご2」「りんご3」のようなユニークキーが作成されます。

STEP3: VLOOKUP関数で2番目以降を取得する

結合キーを使ってVLOOKUPで検索します。

数式: =VLOOKUP($G$2&ROW()-ROW($H$1), $C:$D, 2, FALSE)

• $G$2: 検索したい果物名（ドロップダウンで選択）
• ROW()-ROW($H$1): 行番号から連番を自動生成
• $C:$D: 結合キー列と取得したい列

STEP4: IFERROR関数でエラーを非表示にする

該当データがない行では#N/Aエラーが出るため、IFERRORで空白にします。

数式: =IFERROR(VLOOKUP($G$2&ROW()-ROW($H$1), $C:$D, 2, FALSE), "")

入力規則で検索値の入力ミスを防ぐ方法

ドロップダウンリストの設定手順

1. セルを選択
2. 「データ」タブ → 「データの入力規則」をクリック
3. 「入力値の種類」で「リスト」を選択
4. 「元の値」に果物名のセル範囲を指定
5. OKをクリック

UNIQUE関数（Microsoft 365）で重複なしリストを自動生成

• 数式: =UNIQUE(A2:A100)
• 重複を自動除去したリストが生成される
• ドロップダウンリストの元データとして活用すると便利

INDEX+MATCH関数を使った代替テクニック

VLOOKUPに補助列を追加するのが面倒な場合は、INDEX+MATCH関数を使う方法もあります。

INDEX+MATCHの基本構文

• 数式: =INDEX(戻り範囲, MATCH(検索値, 検索範囲, 0))
• VLOOKUPより柔軟（左方向の検索も可能）

FILTER関数で一発抽出（Microsoft 365限定）

• 数式: =FILTER(B2:B100, A2:A100=G2)
• 補助列不要で、一致するデータを全て一括で返す
• 最も簡単な方法だが、Microsoft 365/Excel 2021以降が必要

Excel作業の効率化に役立つ関連テクニック

VLOOKUPでの複数結果抽出のようなExcel作業を日常的に行っているなら、他のExcelテクニックもマスターしておくと業務効率が大幅にアップします。

【Excel】IFERROR関数の使い方｜エラーを空白や0に変換する方法＆実務活用例5選

【Excel】文字列を結合する方法5選｜&・CONCAT・TEXTJOINの違いと使い分け

複数条件のVLOOKUPをVBAで自動化する方法

VLOOKUP関数では対応しきれない複数条件の検索を、VBAマクロのINDEX+MATCHロジックで自動化できます。

' INDEX+MATCHで複数条件検索するマクロ
Sub 複数条件検索()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim i As Long
    For i = 2 To 4
        Dim key As String
        key = ws.Cells(i, "E") & ws.Cells(i, "F")
        Dim j As Long
        For j = 2 To 7
            If ws.Cells(j, "A") & ws.Cells(j, "B") = key Then
                ws.Cells(i, "G").Value = ws.Cells(j, "C").Value
                Exit For
            End If
        Next j
    Next i
    MsgBox "3件の複数条件検索が完了しました"
End Sub

VBAマクロの実行結果は記事冒頭のインタラクティブデモで確認できます。ボタンを押すとアニメーションで動作をシミュレーションします。

よくある質問（FAQ）

Q. VLOOKUPで3番目のデータだけを取得するには？

補助列の連番を使い、検索値に「りんご3」のように3番目の連番を指定すればOKです。=VLOOKUP("りんご"&3, 結合範囲, 列番号, FALSE) で取得できます。

Q. XLOOKUP関数で2番目以降は取得できますか？

XLOOKUP単体では不可能です。FILTER関数と組み合わせるか、本記事で紹介した補助列を使う方法が有効です。XLOOKUPの基本と応用テクニックは「XLOOKUP関数の使い方完全ガイド」で詳しく解説しています。

Q. 大量データでVLOOKUPが遅い場合の対処法は？

INDEX+MATCH関数に切り替えるか、テーブル参照を使うと高速化できます。10万行以上のデータではFILTER関数も検討してください。

Q. Microsoft 365ならもっと簡単にできますか？

はい。FILTER関数を使えば =FILTER(範囲, 条件) の1行で複数結果を一括抽出できます。補助列も不要です。

Q. VLOOKUPで複数条件（2つの列で検索）を指定するには？

検索値を&演算子で結合し（例: A2&B2）、検索範囲にも同じ結合列を作ることで、複数条件のVLOOKUPが実現できます。

まとめ

VLOOKUPで2番目以降の重複データを抽出する手順は以下の通りです。

1. COUNTIF関数で補助列に連番を作成
2. 検索値+連番の結合キーを作成
3. VLOOKUPで結合キーを検索
4. IFERRORでエラー処理

Excel業務を効率化するなら、関数テクニックをマスターすることが第一歩です。本記事の方法を活用して、VLOOKUPの限界を超えましょう。

関連記事

【Excel】VLOOKUP関数の使い方｜基本から応用まで初心者向けに解説

【Excel】IFERROR関数の使い方｜エラーを空白や0に変換する方法＆実務活用例5選

【Excel】文字列を結合する方法5選｜&・CONCAT・TEXTJOINの違いと使い分け

ExcelのAND関数・OR関数・NOT関数の使い方｜IF関数との組み合わせで複数条件を判定

しんたろ。

Excel歴10年以上 → アプリ開発者

XYouTubeLINEContact

大手メーカーに15年以上勤務。製造部門で海外拠点の立ち上げ支援や、現場責任者として採算管理・納期管理・設備オペレートを経験。 2023年にDX人材育成プログラム第1期生として活動後、現在は製造現場のスケジュール運用を支えるアプリの企画・開発をメインに活動中。工程表示表作成の内製化SaaSを構築し、年間1,300万円のコスト改善を実現。 Excelによる業務改善で年間240時間の残業削減を達成した経験を活かし、ブログやSNSでも情報発信しています。