Excel VLOOKUPで複数結果を取得できない問題の解決と、FILTER関数による効率的な全件表示
VLOOKUP関数は、検索条件に該当する一つの行からデータを取得する関数であり、最初に該当するものを取得するため、検索条件に該当するものが複数ある場合には不適切です。
本記事では、このVLOOKUP関数の制約を克服し、複数の検索結果を全件表示する方法として、以下の二つの解決策を提示します。
- FILTER関数による効率的な解決策（Office 365/2019以降向け）：最も簡潔でメンテナンス性の高い手法。
- VLOOKUP応用によるテクニック（旧バージョンや互換性維持向け）：COUNTIF関数と文字結合を利用した、古典的な問題解決策。
業務の効率化を最優先する場合、利用環境が許せばFILTER関数の利用を推奨します。
【効率的な解決策】Excel FILTER関数で複数結果を自動取得する方法
FILTER関数の基本的な構文と特徴
FILTER関数がVLOOKUP関数の応用テクニックよりも優れている最大の利点は、元のデータ表の構成を変更する必要がないことにあります。
VLOOKUP関数の応用では、複合キーや連番を作成するために元のデータに列を追加する必要がありましたが、FILTER関数は既存のデータ範囲（テーブル）をそのまま参照できます。
この特性により、データ管理の複雑さが大幅に軽減されます。
また、数式が一つで完結し、必要な分だけ結果が自動拡大されるスピル機能に対応するため、非常に便利な関数です。
COUNTIF関数などが不要なため利用できる環境であれば使い勝手が良いです。
FILTER関数の基本構文:
=FILTER(検索する一覧表{項目行を含まない},検索する範囲=検索値)本記事の例示データの場合の数式（F6セルに設定）:
=FILTER(B4:D21,B4:B21=I3)- B4:D21: 検索する一覧表
- B4:B21=I3: 検索する範囲（区分1の列）が、検索値（I3セルの値）と一致する条件
複数結果の全件表示を実現する具体的な手順
- 検索結果を表示したいセル（例: F6）に上記の数式を設定します。
- 検索キーとなるセル（例: I3）に検索したい区分名（例:「哺乳類」）を入力します。
- F6セルに設定した数式がスピル機能により、必要な分だけ自動拡大され、該当する全ての行のデータを自動的に表示します。
メリット: 複数列表示にも自動対応するため非常に便利です。検索結果の最大行数を事前に把握する必要がなく、元のデータに列を追加する必要もありません。
VLOOKUP応用で複数結果に対応するテクニック（旧バージョン・互換性維持向け）
VLOOKUPが単一結果しか返せない構造的な理由
VLOOKUP関数は検索条件に該当する一つの行からデータを取得する関数であり、もっとも最初に該当するものを取得するため、検索条件に該当するものが複数ある場合には不適切です。
複合キーと連番を利用した複数結果の取得手順
この応用テクニックは、VLOOKUP関数の制限を回避するため、検索対象の行ごとに固有の「複合検索キー」を作成します。
手順A: 検索対象表に連番（No.）を振る
区分1の隣（例: C列）に連番のセルを用意します。
C3セルにCOUNTIF関数を設定し、同一の区分1が出現するたびにカウントアップされるようにします。
C3セルの数式:
=COUNTIF($D$3:D3,D3)ロジック: 検索範囲の始点のみが絶対参照となっていて、下に行くほどカウントの範囲が広がるようになっています。
C3セルをデータの一番下までコピーします。
手順B: 連番と区分1を結合して複合検索キーを作成する
連番セルと区分1を & で文字結合し、一つの複合検索キーを作成します。
D4セルの数式（例: E4セルが区分1、C4セルが連番の場合）:
=E4&C4結果:「哺乳類1」「両生類1」「哺乳類2」といった固有のキーが作成されます。
手順C: 検索用シートで VLOOKUP を設定する
検索用シートにも、固定の連番（No.）と、検索値（区分1）を結合した複合検索キーを作成します。
検索用シートのJ4セルの数式（例: 区分1がK4セルの場合）:
=K4&I4
検索用シートの「区分2」のセルに VLOOKUP（または XLOOKUP）を設定します。
VLOOKUPの数式例（M4セル）:
=VLOOKUP($J4,$D$4:$G$21,3,FALSE)この数式を、連番の最大値（例: 5）までコピーして、全ての検索結果を表示させます。
VLOOKUP応用テクニックの特徴
メリット: Excelの旧バージョンでも実行可能であり、互換性を維持できます。
デメリット:
- 検索結果の最大値（例: 哺乳類の 5 件）を事前に MAX関数などで把握し、連番を用意しておく必要があります。
- 存在する数が最大値に満たない場合、結果は #N/A エラーとなるため、IFERROR 関数などで対処しておく必要があります。
- 単純な数式では解決しないため、特殊な表構造が必要になる。
どちらの関数を利用すべきか? 効率性を基準とした比較
FILTER関数とVLOOKUP応用テクニックの機能比較表
| 項目 | FILTER関数 | VLOOKUP応用テクニック | 評価基準 |
|---|---|---|---|
| 対応バージョン | × Office 365 / Excel 2019以降 | 〇 全バージョン対応 | 互換性 |
| 数式の簡潔性 | 〇 (数式1行) | × (複雑) | 構築の容易性 |
| 元のデータへの影響 | 〇 (データ追加不要) | × (連番・複合キー追加が必須) | データ構造の維持 |
| メンテナンス性 | 〇 (高) | × (低) | 管理の容易性 |
| 最大行数の事前把握 | 〇 (不要) | × (必須) | 設定の手間 |
結論: Office 365やExcel 2019以降の環境であれば、簡潔さ、メンテナンス性、および効率性の観点からFILTER関数を利用することが、最も合理的な選択です。
簡易的な検索ならExcelフィルター機能で十分なケース
単純に探したい場合はフィルター機能を使用するのが一番適切です。
検索結果を別シートに転記する必要がなく、単に元の表内で条件に合うデータを確認したいだけの場合は、Excelのフィルター機能で十分です。
しかし、フィルター機能は検索先の表の表示・非表示を直接切り替えるものであり、検索先の表を操作したくない場合や、検索をシートに組み込んで固定させたい場合は、関数の方が適しています。
まとめ
Excelで複数の検索結果を取得する場合、最新の環境であればFILTER関数が数式の記述とメンテナンスの効率化を支援します。
旧バージョン環境や互換性が必要な場合は、COUNTIF関数と文字結合を利用したVLOOKUP関数の応用テクニックで問題に対処可能です。
関連記事
「VLOOKUP関数 複数」についてのガイド
XLOOKUP(VLOOKUP)関数の使い方・活用方法の記事一覧
Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。