VLOOKUP関数から進化したXLOOKUP関数

ExcelのXLOOKUP（エックス・ルックアップ）関数は、特定のデータを指定したセル範囲から探して、対応する値を取り出す関数です。商品リストから商品IDに対応する商品名や単価を取得したり、顧客リストから顧客IDに対応する名前や住所を取得したりしたいときに役立ちます。

似た働きをする関数に、「ビジネスに必須」として広く知られているVLOOKUP（ブイ・ルックアップ）関数があります。VLOOKUP関数は、表を縦方向（Vertical）に検索し、特定のデータに対応する値を取り出す機能を持っています。

XLOOKUP関数はVLOOKUP関数の後継として、2020年に登場した新しい関数です。VLOOKUP関数だけでなく、表を横方向（Horizontal）に検索するHLOOKUP（エイチ・ルックアップ）関数の機能も備えており、引数の扱いも分かりやすくなりました。

また、VLOOKUP関数のように検索値を表の左端に配置する必要がありません。今後はXLOOKUP関数のほうが主流になっていくと思われ、もし「VLOOKUP関数に慣れているから」という理由だけで使っていないとしたら、非常にもったいないと言えるでしょう。この機会に、XLOOKUP関数をマスターすることをおすすめします。

本記事ではXLOOKUP関数を「ぜひ試してみたい」「VLOOKUP関数から乗り換えたい」という人のために、XLOOKUP関数の使い方を解説します。

XLOOKUP関数の対応環境

本記事執筆時点において、XLOOKUP関数はMicrosoft 365のExcelとExcel 2021、Web版のExcelで利用可能です。

お使いのExcelがXLOOKUP関数に対応しているかは、任意のセルに「=X」と入力し、候補の一覧にXLOOKUP関数が表示されるかどうかで判断できます。

XLOOKUP関数が対応しているかを確認する

Excelを起動して任意のセルに「=X」と入力し、一覧に「XLOOKUP」と表示されれば、お使いのバージョンでXLOOKUP関数を利用できます。

XLOOKUP関数の構文

続いて、XLOOKUP関数の構文を確認しましょう。

---

=XLOOKUP（検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード）

---

VLOOKUP関数の引数が4つだったのに対し、XLOOKUP関数の引数は6つもあります。「逆に難しいのでは？」と感じるかもしれませんが、必須なのは最初の3つのみで、4つめ以降の引数は省略可能です。それぞれの引数には以下のような意味があります。

1. ［検索値］ ...... どのデータで
2. ［検索範囲］ ...... どこを検索して
3. ［戻り範囲］ ...... どの範囲の値を取り出すか
4. ［見つからない場合］ ...... 検索値が見つからない場合、何を表示するか
5. ［一致モード］ ...... 完全一致か、近似値も検索するか（初期値：完全一致）
6. ［検索モード］ ...... どのような順序で検索するか（初期値：先頭から末尾）

簡単な例で、XLOOKUP関数の動作を見てみましょう。以下のXLOOKUP関数の数式で指定している引数は、最初の3つのみです。

XLOOKUP関数の利用例

セルB2には「=XLOOKUP(A2,B6:B40,C6:C40)」という数式が入力されています。セルA2に入力した会員ID「T017」に対応する氏名を表（顧客リスト）から取り出す、という意味です。

このXLOOKUP関数の数式を読み解くと、以下のようになります。

まず、1つめの引数［検索値］として、探したい「会員ID」が入力されています。ここではセルA2を参照しています。

2つめの引数［検索範囲］では、検索値となる「会員ID」が入力されたセル範囲を指定しています。ここではセルB6〜B40が該当します。

3つめの引数［戻り範囲］では、取得したい値である「氏名」が入力されたセル範囲を指定しています。ここではセルC6〜C40が該当します。

VLOOKUP関数をマスターしている人なら、VLOOKUP関数の3つめの引数［列番号］が、XLOOKUP関数では［戻り範囲］に相当していることが分かると思います。列番号を表の左側から数える必要がなく、値を取り出すセル範囲を直接指定すればいいので、より分かりやすいのではないでしょうか。

ただし、XLOOKUP関数では［検索範囲］と［戻り範囲］の高さを揃えておく必要があります。異なる高さのセル範囲を指定すると、以下の画面のように「#VALUE!」エラーとなるので注意してください。

XLOOKUP関数のエラーの例

先ほどのXLOOKUP関数の数式で、［検索範囲］を「B6:B40」、［戻り範囲］を「C6:C41」と指定しました。［戻り範囲］のほうが［検索範囲］よりも1行多く、高さが異なっています。この状態で数式を確定すると......

このように「#VALUE!」エラーが表示されてしまいます。

XLOOKUP関数の入力方法

今度は、実際にXLOOKUP関数を入力する手順を見ていきましょう。先ほどの数式をあらためて記載すると、以下のようになります。この数式を入力するには、その下の手順のように操作します。

  =XLOOKUP(A2,B6:B40,C6:C40)
  

1数式の入力を開始する

XLOOKUP関数で取得する値を表示したいセル（ここではセルB3）を選択し、「=XLOOKUP」と入力します。

2第1引数［検索値］を指定する

「(」を入力し、1つめの引数［検索値］となるセル（ここではセルA2）をクリックします。直接「A2」と入力しても構いません。

3第2引数［検索範囲］を指定する

「,」を入力し、2つめの引数［検索範囲］となるセル範囲（ここではセルB6～B40）をドラッグして選択します。選択範囲を間違えたときは、ドラッグをやり直せばOKです。

4第3引数［戻り範囲］を指定する

「,」を入力し、3つめの引数［戻り範囲］となるセル範囲（ここではセルC6～C40）をドラッグして選択します。最後に、末尾に「)」を入力して［Enter］キーを押します。

5数式の入力が完了した

XLOOKUP関数の数式が完成し、セルB2に入力した会員ID「T017」に対応する氏名が表示されました。

スピルで複数の結果を表示する

XLOOKUP関数は、隣接するデータを複数のセルにまとめて取り出せる「スピル」機能に対応しています。例えば、検索値の「会員ID」に対応する「氏名」「利用店舗」「会員ランク」を取り出したいとき、1つの数式を入力するだけで済みます。

XLOOKUP関数とスピル機能の組み合わせ

セルC2に入力された1つのXLOOKUP関数の数式で、「会員ID」に対応する「氏名」「利用店舗」「会員ランク」をまとめて取得できています。これがスピル機能です。

ちなみに、スピル（spill）とは「こぼれる」という意味で、隣接するセルに複数のデータが「こぼれる」ように表示されることに由来しています。

XLOOKUP関数とスピル機能を使って、複数のデータを取り出す例を見ていきましょう。ここでは「会員ID」に対応する「氏名」「利用店舗」「会員ランク」をまとめて取り出します。手順としては、先ほど入力したXLOOKUP関数の数式を以下のように書き換えるだけです。

  =XLOOKUP(A2,B6:B40,C6:E40)
  

1XLOOKUP関数の数式を修正する

セルB2の数式「=XLOOKUP(A2,B6:B40,C6:C40)」を「=XLOOKUP(A2,B6:B40,C6:E40)」に修正し、［Enter］キーを押します。3つめの引数［戻り範囲］を、C列単体からC列～E列の3列分に変更しています。

2複数のデータを取り出せた

会員ID「T017」に対応する「氏名」「利用店舗」「会員ランク」をまとめて取り出せました。数式を入力したセルを選択すると、スピルで表示されたセルが浮き出たように表示されます。

複数のデータを求めるとなると、複数のセルに数式を入力する必要があるように思えますが、オートフィルで数式をコピーする必要はありません。3つめの［戻り範囲］を複数の値を含むように指定すると、対応する値がまとめて表示されます。

ただし、スピルで結果が表示されるセル範囲にすでにデータが入力されていると、「#スピル!」または「#SPILL!」エラーが表示されます。また、スピルの結果は「ゴースト」と呼ばれ、直接編集できないので注意してください。

スピル機能のエラーの例

スピルの結果が表示されるセル範囲にデータが入力されていると、「#スピル!」エラーが表示されます。

検索値より左側のデータを検索する

冒頭でも触れた、XLOOKUP関数を使って［検索値］よりも左側にあるデータを取り出す方法について、あらためて解説します。これは［検索範囲］と［戻り範囲］をセル範囲として別々に指定するXLOOKUP関数ならではの処理で、VLOOKUP関数ではできません。

ここでは「会員ID」に対応する「No」を取り出します。入力するXLOOKUP関数の数式は以下の通りです。

  =XLOOKUP(B2,B6:B40,A6:A40)
  

XLOOKUP関数で検索値より左側のデータを検索した例

XLOOKUP関数の数式はセルA2に入力されており、［検索値］はセルB2に入力した「会員ID」です。［検索範囲］は「会員ID」が入力されているセルB6～B40、［戻り範囲］は「No」が入力されているセルA6～A40です。顧客リストの表で、取得したい値である「No」は「会員ID」よりも左側にありますが、問題なく値を取得できています。

「該当なし」のメッセージを表示する

ここまでは必須の引数のみを使った例を見てきましたが、4つめの引数［見つからない場合］の利用例も紹介しましょう。この引数を指定すれば、検索値が見つからない場合にエラーではなく「該当なし」といったメッセージを表示できます。

引数に文字列を指定する場合、半角の「"」（ダブルクォーテーション）で囲むことを忘れないでください。XLOOKUP関数の数式は以下のようになります。

  =XLOOKUP(A2,B6:B40,C6:E40,"該当なし")
  

XLOOKUP関数の第4引数で「該当なし」のメッセージを表示した例

XLOOKUP関数の数式はセルB2に入力されており、［検索値］はセルA2に入力した「会員ID」です。［検索範囲］は「会員ID」が入力されているセルB6～B40、［戻り範囲］は「氏名」「利用店舗」「会員ランク」が入力されているセルC6～E40です。さらに、4つめの引数［見つからない場合］に「"該当なし"」と指定しています。セルA2の会員IDと一致する値がないため、「該当なし」と表示されました。

［一致モード］と［検索モード］の種類

最後に、5つめの引数［一致モード］と、6つめの引数［検索モード］についても説明しておきましょう。

5つめの引数［一致モード］は、［検索値］に対する判定基準を指定します。指定できる値は以下の通りで、省略した場合は「0」（完全一致）となります。

• 0 ...... 完全一致
• -1 ...... 完全一致、または次に小さい項目が一致
• 1 ...... 完全一致、または次に大きい項目が一致
• 2 ...... ワイルドカード文字と一致

「-1」と「1」は、近似一致として動作します。例えば、予算表などで指定した値を超えない（または超える）値を一致と見なすときに利用します。

「2」は「*宿」のようなワイルドカードを含む文字列を検索できます。［検索値］に「*宿」と指定して、「新宿」や「原宿」を検索したい場合に利用します。

6つめの引数［検索モード］は、［検索範囲］の検索方向を指定します。指定できる値は以下の通りで、省略した場合は「1」（先頭から末尾）となります。

• 1 ...... 先頭から末尾
• -1 ...... 末尾から先頭
• 2 ...... バイナリ検索（昇順で並べ替え）
• -2 ...... バイナリ検索（降順で並べ替え）

「-1」と指定した場合は、末尾から先頭に検索します。「2」「-2」は、例えば数万件以上のデータを高速で検索したいときに利用します。ただし、［検索範囲］を昇順、または降順で並べ替えておく必要があります。

関連記事