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 2021Web版のExcelで利用可能です。

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

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

ExcelのXLOOKUP関数の使い方

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

XLOOKUP関数の構文

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


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


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

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

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

XLOOKUP関数の利用例

Excelの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関数のエラーの例

ExcelのXLOOKUP関数の使い方

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

ExcelのXLOOKUP関数の使い方

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

XLOOKUP関数の入力方法

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

  =XLOOKUP(A2,B6:B40,C6:C40)
  
1数式の入力を開始する

ExcelのXLOOKUP関数の使い方

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

2第1引数[検索値]を指定する

ExcelのXLOOKUP関数の使い方

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

3第2引数[検索範囲]を指定する

ExcelのXLOOKUP関数の使い方

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

4第3引数[戻り範囲]を指定する

ExcelのXLOOKUP関数の使い方

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

5数式の入力が完了した

ExcelのXLOOKUP関数の使い方

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

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

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

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

ExcelのXLOOKUP関数の使い方

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

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

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

  =XLOOKUP(A2,B6:B40,C6:E40)
  
1XLOOKUP関数の数式を修正する

ExcelのXLOOKUP関数の使い方

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

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

ExcelのXLOOKUP関数の使い方

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

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

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

スピル機能のエラーの例

ExcelのXLOOKUP関数の使い方

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

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

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

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

  =XLOOKUP(B2,B6:B40,A6:A40)
  
XLOOKUP関数で検索値より左側のデータを検索した例

ExcelのXLOOKUP関数の使い方

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

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

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

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

  =XLOOKUP(A2,B6:B40,C6:E40,"該当なし")
  
XLOOKUP関数の第4引数で「該当なし」のメッセージを表示した例

ExcelのXLOOKUP関数の使い方

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」は、例えば数万件以上のデータを高速で検索したいときに利用します。ただし、[検索範囲]を昇順、または降順で並べ替えておく必要があります。

関連記事

【エクセル時短】そろそろ知っておきたい「スピル」の基本。Excel 2021/2019で使える新機能を試そう

Excel 2021/2019、およびMicrosoft 365のExcelで使える新機能「スピル」について、用語として見かけた、聞いたことがあるという人も多いのではないでしょうか。今回はスピルの動作をやさしく解説し、実務に活用する方法を解説します。

VLOOKUP関数の使い方

ExcelのVLOOKUP(ブイ・ルックアップ)関数は、表を縦方向に検索し、特定のデータに対応する値を取り出す機能を持ちます。業務の効率化に役立つ関数として人気がありますが、引数の指定方法が複雑という難しさもあります。使い方をあらためて確認しましょう。

Excel関数 機能別一覧(全510関数)

すべてのエクセル関数を、「数学/三角関数」「論理関数」「統計関数」といった機能ごとの分類でまとめた一覧です。Microsoft 365(旧称:Office 365)専用の関数、およびExcel 2021~2013に対応した関数と、その使い方解説記事を探せます。