XLOOKUPやVLOOKUPで「複数の検索値」を指定する方法

XLOOKUPやVLOOKUPを使ってデータを取得する際に「複数の検索値」を指定したい場合もあるだろう。今回は、その具体的な手順と考え方について紹介していこう。前回の連載でもXLOOKUPに「複数の検索値」を指定する方法を紹介しているが、このテクニックを正しく活用するには、その仕組みについて十分に理解しておく必要がある。

XLOOKUPやVLOOKUPに指定できるキーワードは1個だけ

「XLOOKUP」や「VLOOKUP」といった関数を使ってデータを取得するときは、検索値（キーワード）を1個だけ指定するのが基本となる。とはいえ、「複数の検索値」を条件にデータを取得したい場合もあるだろう。

このような場合は「&」（アンド）を上手に使うと、「複数の検索値」にも対応できるようになる。今回は、その具体的な手順を紹介していこう。

「複数のキーワード」でXLOOKUPやVLOOKUPを使用する方法

なお、このテクニックを使用するときは「&」が果たす役割について理解しておく必要がある。これを理解できていないと、「複数の検索値」が正しく機能してくれない恐れがある。逆に、仕組みを十分に理解できていれば、LOOKUP系以外の関数にも使えるテクニックとして色々な場面に応用できるはずだ。

関数XLOOKUPに「複数の検索値」を指定する方法

まずは、例として用いるデータ表から紹介していこう。このデータ表は前回の連載と同じもので、日々の売上金額を店舗別、時間帯別に記録したものだ。さらに、同じ「店舗」、同じ「時間帯」における売上ランキングを求めた「分類別順位」も記載されている。

今回は、このデータ表から「店舗」、「時間帯」、「分類別順位」の3つを条件にデータを取得してみよう。なお、それぞれの条件（検索値）は、B1セル（店舗）、C1セル（時間帯）、B4セル（分類別順位）で指定することにする。

データ取得に使用する「検索値」と「データ表」

さっそく、具体的な手順を解説していこう。前回の連載では、以下の図のように関数XLOOKUPを記述して「複数の検索値」によるデータ取得を実現した。

前回の連載で紹介した関数XLOOKUPの記述

オートフィルで関数をコピーできるように、それぞれのセル範囲を「絶対参照」で指定している。ただし、このままでは読み取りづらいので、セル範囲を「相対参照」で記述した例を使って解説していこう。

引数を相対参照で記述した場合

念のため、関数XLOOKUPの書式も掲載しておくと以下のようになる。

◆関数XLOOKUPの書式
=XLOOKUP(検索値, 検索範囲, 取得範囲, [#N/A代替], [一致モード], [検索順])

先ほど示した例の場合、検索値は「B1&C1&B4」、検索範囲は「C9:C50&D9:D50&F9:F50」、取得範囲は「E9:E50」となる。つまり、検索値と検索範囲に該当する部分を「&」（アンド）でつなぐことで「複数の検索値」に対応している訳だ。

では、この「&」はどのような役割を担っているのだろうか？　もしかすると、「&でつなぐと複数の検索値に対応できる」と勘違いしている方もいるかもしれない。しかし、実際の挙動はそうではない。

「&」は「文字列データの結合」を行う演算子であり、Excelの“標準的な機能”のひとつである。XLOOKUPに用意されている“特別な機能”ではない。

もういちど詳しく順番に見ていこう。関数XLOOKUPの「検索値」となる第1引数は、以下の図のように記述されている。

第1引数（検索値）の記述

この内容を確認するために、適当なセルに「=B1&C1&B4」と入力してみると、以下の図のような結果が得られた。

関数XLOOKUPの「検索値」になるデータを確認する数式

関数XLOOKUPの「検索値」

つまり、B1セル、C1セル、B4セルのデータを結合した「新宿店ランチ1」という文字列が関数XLOOKUPの「検索値」として指定されることになる。

続いては、「検索範囲」となる第2引数について見ていこう。こちらの記述は以下の図のようになっている。

第2引数（検索範囲）の記述

先ほどと同様に、この記述についても内容を確認していこう。データ表の1行目と同じ行に「=C9:C50&D9:D50&F9:F50」と入力して「Enter」キーを押す。

関数XLOOKUPの「検索範囲」になるデータを確認する数式

すると、以下の図のような結果が得られた。各行の「店舗」、「時間帯」、「分類別順位」を結合した形で「1列のデータ表」（配列）が作成されるのを確認できるだろう。

関数XLOOKUPの「検索範囲」（仮想の検索範囲）

関数XLOOKUPは、このデータ表を“仮想の検索範囲”として扱うことになる。

通常、XLOOKUPを使用するときは“実在するデータ表の一部”を検索範囲として指定する。一方、今回の例では「店舗＋時間帯＋分類別順位」の結合データで作成した“仮想の検索範囲”を指定することになる。

そして、この“仮想の検索範囲”の中から「検索値」（新宿店ランチ1）に一致するデータを探し出し、それと同じ行にあるデータを取得する、という仕組みになる。今回の例では、取得範囲に「E9:E50」を指定しているので、「売上金額」の列で“同じ行”にある「\162,570」のデータが取得されることになる。

「検索値に該当するデータ」と「取得されるデータ」

もういちど各引数の内容について要約しておこう。

◆第1引数
それぞれのキーワードを「&」で結合した文字列データを「検索値」として指定する

◆第2引数
「検索値」と同じ形式になるように、各データ範囲を「&」で結合した“仮想の検索範囲”を指定する

◆第3引数
データの「取得範囲」は普通に指定する

要するに、文字列を結合する「&」を上手に利用することで「複数の検索値」に対応させるのが今回のテクニックの要となる。決して関数XLOOKUPに「複数の検索値」に対応する機能が用意されている訳ではない。勘違いしないように注意しておこう。

関数VLOOKUPに「複数の検索値」を指定する方法

続いては、関数VLOOKUPを「複数の検索値」に対応させる方法を紹介していこう。関数XLOOKUPが使えないExcel 2019以前のバージョンでは、以下に示す方法で「複数の検索値」に対応しなければならない。

念のため、関数VLOOKUPの書式を紹介しておくと以下のようになる。

◆関数VLOOKUPの書式
=VLOOKUP(検索値, データ範囲, 列番号, TRUE/FALSE)

まずは、先ほどと同様に「&」を使ってデータを結合する方法を試してみよう。以下の図のように関数VLOOKUPを入力する。

先ほどと同じ手法で関数VLOOKUPを入力した例

その後、「Enter」キーを押して関数VLOOKUPを実行すると、「#REF!」のエラーが発生するのを確認できる。

関数VLOOKUPの結果（エラー）

このような結果になるのは、XLOOKUPとVLOOKUPで「引数の指定方法」が異なることが原因だ。先ほどの関数VLOOKUPについて「検索値」と「データ範囲」の指定内容を確認してみよう。

関数VLOOKUPの「検索値」と「データ範囲」

検索値には「新宿店ランチ1」という文字列が指定される。これを“仮想のデータ範囲”の中から探し出すことになる。ここまでの処理は特に問題ない。問題となるのは、その次の処理だ。

先ほど示した例では、関数VLOOKUPの第3引数に「4」を指定している。よって、データ範囲の4列目からデータが取得されることになる。一方、“仮想のデータ範囲”には1列分のデータしかなく、4列目は存在しない。その結果、エラーが発生してしまうのだ。

試しに、関数VLOOKUPの第3引数を「1」に変更した例も紹介しておこう。この場合、エラーが発生せず、「検索値」と同じデータが取得されるのを確認できる。

「列番号」を1に変更した例

関数VLOOKUPの結果

とはいえ、このようなデータを取得しても意味がない。取得したいデータは「売上金額」となるが、このデータは“仮想のデータ範囲”に含まれていないため取得できない。

これは、取得データを「データ範囲のN列目」で指定する仕様になっている以上、どうしようもない問題だ。XLOOKUPのように「検索範囲」と「取得範囲」を個別に指定できないことが最大の原因といえる。

それ以前の問題として、Excel 2019以前はスピルに対応していないため、「C9:C50&D9:D50&F9:F50」の記述そのものもエラーの原因になる。上図は、Excel 2021の環境で関数VLOOKUPを実行した例であり、Excel 219以前では「#VALUE!」のエラーが発生してしまう。

よって、別の解決法を探る必要がある。具体的には“仮想の検索範囲"を“実在するデータ”として自分で作成しなければならない。データ表の左端に「データ検索用」の列を作成し、第1引数（検索値）と同じ順番でデータを「&」で結合する。