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

2019年6月26日

概要

VLOOKUP関数は検索条件に該当する

一つの行からデータを取得する関数です。

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

検索条件に該当するものが

複数ある場合には不適切です。

単純に探したい場合はフィルター機能を

使用するのが一番適切です。

しかしフィルター機能は検索先の表の

表示・非表示を直接切り替えるものであり

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

検索先の表を操作したくない場合や、

検索をシートに組み込んで

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

そこでVLOOKUP関数の応用で

複数の結果に対応する方法を紹介します。

なおHLOOKUP関数でも同様のことが可能です。

手順

下記の表を例にします。

区分1を検索キーとします。

検索先の表のサンプル

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

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

COUNTIF関数を利用して同一区分1が

カウントアップされていくようにします。

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

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

C1に下記の数式を設定し

表の一番下までコピーします。

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

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

下に行くほどカウントの範囲が

広がるようになっています。

なおこの№が最大で幾つまであるのか

把握しておく必要があるため

MAX関数で最大値を取得します。

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

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

先ほどの連番と区分1を&で文字結合し、

一つの列とします。(追加したD列)

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

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

区分1を一つ入力したら全ての該当データが

表示されるようにします。

下記のような検索用シートを作ります。

左端の№は固定の数値です。

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

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

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

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

なおこの数式は列を作らず、

VLOOKUP関数に直接、記述しても構いません。

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

検索対象表と検索用シートの両方の検索キーで

VLOOKUP関数を設定します。

VLOOKUP関数を設定

№を最大値まで用意しておかないと

全ての検索結果が表示されない点に

注意が必要です。

また存在する数が最大値に満たないと

#N/Aエラーとなるため

必要であればIFERROR関数などで

対処しておきましょう。

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

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

先ほどの例では検索する区分1は一つでしたが、

列ごとに区分1を指定する例を記述します。

まず検索用シートは下記のようにします。

区分1の列を追加しています。

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

検索対象表と同様に№をCOUNTIF関数で

振るように数式を作り、それと区分1を結合します。

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

検索値が1つの場合と同様の

VLOOKUP関数を作れば完成です。

検索用シート側に最大値まで№がないと

検索結果が漏れてしまう点と

存在しない№が出た場合は

エラーとなってしまう点は

検索値が一つの場合と同様です。

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

関連記事

VLOOKUP関数の記事一覧

検索条件に一致するセルの件数を取得(COUNTIF関数)