Excel VLOOKUP関数で複数の結果を取得する方法

2020年5月13日

VLOOKUP関数は検索条件に該当する一つの行からデータを取得する関数です。

もっとも最初に該当するものを取得するため

検索条件に該当するものが複数ある場合には不適切です。

そこでVLOOKUP関数の応用で複数の結果に対応する方法を紹介します。

別の方法の方が効率が良い場合もあるため、そちらも併せて紹介します。

手順

下記の表を例にします。区分1を検索キーとします。

検索先の表のサンプル

XLOOKUP関数(VLOOKUP)で実施

検索対象の列に連番を振る

区分1にの隣に連番のセルを用意します。

COUNTIF関数を利用して同一区分1がカウントアップされていくようにします。

検索キーに連番セルを追加

C列が連番セル、B列がC列の数式を表示したものです。

C1に下記の数式を設定し表の一番下までコピーします。

=COUNTIF($D$3:D3,D3)

検索範囲の始点のみが絶対参照となっていて

下に行くほどカウントの範囲が広がるようになっています。

なおこの№の最大値を把握しておく必要があるためMAX関数で最大値を取得します。

MAX関数で№の最大値を取得

連番と検索対象を文字結合して検索キーを作る

先ほどの連番と区分1を&で文字結合し、一つの列とします。(追加したD列)

連番と検索対象を文字結合して検索キーを作る

一つの検索値から取得する場合

区分1を一つ入力したら全ての該当データが表示されるようにします。

下記のような検索用シートを作ります。左端の№は固定の数値です。

1から№の最大値まで用意する必要があります。

一つの検索値から取得する場合の検索用シート

文字結合で検索用シートの検索キーを作ります。

区分1の方は絶対参照で式がズレないようにします。

文字結合で検索用シートの検索キーを作成

検索対象表と検索用シートの両方の検索キーでXLOOKUP関数VLOOKUP関数)を設定します。

VLOOKUP関数を設定

№を最大値まで用意しておかないと全ての検索結果が表示されない点に注意が必要です。

また存在する数が最大値に満たないと#N/Aエラーとなるため

必要であればIFERROR関数などで対処しておきましょう。

一つの検索値から取得する場合の完成

複数の検索値から取得する場合

先ほどの例では検索する区分1は一つでしたが、列ごとに区分1を指定する例を記述します。

まず検索用シートは下記のようにします。区分1の列を追加しています。

複数の検索値から取得する場合の検索用シート

検索対象表と同様に№をCOUNTIF関数で振るように数式を作り、それと区分1を結合します。

COUNTIFで№を作り、文字列結合で検索キーを作成

検索値が1つの場合と同様のXLOOKUP関数VLOOKUP関数)を作れば完成です。

検索用シート側に最大値まで№がないと検索結果が漏れてしまう点と

存在しない№が出た場合はエラーとなってしまう点は検索値が一つの場合と同様です。

複数の検索値から取得する場合の完成

別解:フィルター機能で実施

単純に探したい場合はフィルター機能を使用するのが一番適切です。

フィルター機能で哺乳類を探す例
フィルター機能で哺乳類を探す例

しかしフィルター機能は検索先の表の表示・非表示を直接切り替えるものであり

条件をこまめに変えて調べる用途に向いているので

検索先の表を操作したくない場合や、検索をシートに組み込んで

固定させたい場合は関数の方が良いです。

別解:FILTER関数で行う

Office365やExcel2019以降であればFILTER関数が利用可能です。

COUNTIF関数などが不要なため利用できる環境であれば使い勝手が良いです。

数式は下記のようにします。

=FILTER(検索する一覧表{項目行を含まない},検索する範囲=検索値)

今回の例では下記になります。(中間セルは削除)

=FILTER(B4:D21,B4:B21=I3)

一つのセル(例ではF6)に設定すればスピルで必要な分だけ自動拡大されます。

複数列表示にも自動対応するため非常に便利な関数です。

FILTER関数で実施する例
FILTER関数で実施する例

関連記事

「VLOOKUP関数 複数」についてのガイド

XLOOKUP(VLOOKUP)関数の使い方・活用方法の記事一覧