XLOOKUP関数の解説

ExcelのXLOOKUP関数は、配列内の項目と一致する項目を検索し、その対応する値を返すための新しい関数です。この記事では、XLOOKUP関数の基本的な使用方法や応用例を詳細に解説します。

関数カテゴリー

XLOOKUP関数は「検索/行列関数」のカテゴリーに属します。このカテゴリーの関数は、データ内の特定の情報を検索または参照するための関数群です。

処理の概要

XLOOKUP関数は、指定した値と一致する項目の値を配列内で返します。従来のVLOOKUP関数やHLOOKUP関数に比べて、一致モードや検索モードのカスタマイズが容易です。

意義

XLOOKUP関数は、データの検索や参照がより柔軟に、そして簡単になります。特に大きなデータセットや複雑なデータ構造の中での検索に威力を発揮します。

構文・書式

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

引数

検索値: 検索する値。
検索範囲: 検索値を検索する配列。
戻り範囲: 検索値に対応する値を返す配列。複数列あるいは複数行の指定をするとスピル形式で結果を表示するようになる。
見つからない場合 (オプション): 検索値が見つからない場合に返す値。省略した場合はエラーが返されます。
一致モード (オプション): 一致する値の種類を指定します。省略すると、デフォルトで「0」となり、完全一致のみを検索します。また、「-1」（以下）および「1」（以上）のいずれかを指定することで、完全一致する値がないケースで最も近い値に対する一致を検索することができます。「2」では部分一致が可能です。
検索モード (オプション): 検索を行う方向を指定します。省略すると、デフォルトで「1」となり、最初から最後まで検索します。また、-1（最後から最初まで）を指定することができます。

関数の使用サンプル

基本形(検索値, 検索範囲, 戻り範囲)

    =XLOOKUP("田中", A1:A10, B1:B10)

この例では、範囲A1:A10内で「田中」という名前を検索し、一致する位置のB列の値を返します。一致モードも検索モードも指定していないので「完全一致・上から」の位置検索となります。「田中」が複数ある場合は最上位のものが採用されます。

引数「見つからない場合」に何かしらの値を設定する例

    =XLOOKUP("鈴木", A1:A10, B1:B10, "該当者なし")

この例では、範囲A1:A10内で「鈴木」という名前を検索し、一致する位置のB列の値を返します。見つからない場合は「該当者なし」を返します。

引数「一致モード：-1」の例

    =XLOOKUP(5000, A1:A10, B1:B10, "該当者なし", -1)

この例では、範囲A1:A10内で「5000」以下で最も近い値を検索し、一致する位置のB列の値を返します。見つからない場合は「該当者なし」を返します。

引数「一致モード：1」の例

    =XLOOKUP(100, A1:A10, B1:B10, "該当者なし", 1)

この例では、範囲A1:A10内で「5000」以上で最も近い値を検索し、一致する位置のB列の値を返します。見つからない場合は「該当者なし」を返します。

引数「検索モード：-1」の例

    =XLOOKUP("田中", A1:A10, B1:B10, "該当者なし", 0, -1)

この例では、範囲A1:A10内で「田中」という名前を検索し、一致する位置のB列の値を返します。ただし、検索は最後(下、あるいは右)から始まります。見つからない場合は「該当者なし」を返します。「田中」が複数ある場合は最下位(あるいは再右)のものが採用されまるのです。

参考：引数「一致モード：ワイルドカード」の使用例

“*” の使用例と解説

    =XLOOKUP("*田中", A1:A10, B1:B10, ,2)

この例では、範囲A1:A10内で「田中」で終わる名前を検索し、一致する位置のB列の値を返します。「*」は何が入ってもよいことを示します。

“?” の使用例と解説

    =XLOOKUP("?田中", A1:A10, B1:B10, ,2)

この例では、範囲A1:A10内で二文字目が「田中」となる名前を検索し、一致する位置のB列の値を返します。「?」は任意の一文字を示します。

“˜” の使用例と解説

    =XLOOKUP("˜*田中", A1:A10, B1:B10, ,2)

この例では、範囲A1:A10内で「*田中」という名前を検索し、一致する位置のB列の値を返します。「˜」はワイルドカード文字自体を検索する際に使用します。つまり、ここでは「*」はワイルドカードとしてではなく、文字として扱われます。

戻り配列に複数列を設定してスピル機能を使う

Excelのスピル機能は、XLOOKUP関数と共に使用することで、戻り配列に複数列を設定し、一度に複数の結果を得ることができます。この機能は、特に一致する行に対して複数の関連データを取得する必要がある場合に非常に便利です。

以下の表を例に取ります。

名前	年齢	職業	住所
田中	25	教師	東京都
佐藤	32	エンジニア	神奈川県
鈴木	29	医者	大阪府
高橋	41	会計士	福岡県

この表では、名前に基づいてその人の年齢、職業、住所を一度に取得したいとします。その場合、次のようなXLOOKUP関数を使用します。

    =XLOOKUP("田中", A2:A5, B2:D5)

この式では、名前が「田中」の人の年齢（25歳）、職業（教師）、住所（東京都）が一度に取得できます。

このように、XLOOKUP関数の戻り範囲に複数列を設定すると、一度に複数の情報を取得することが可能となります。これにより、複数の関連データを効率的に処理することができます。

ただし、この機能はExcelの最新バージョンでのみ使用可能であり、また、戻り範囲となる列の数は全て同じである必要があります。それらを満たさない場合、正しく動作しない可能性があるため、注意が必要です。

類似している関数

VLOOKUP関数: 指定した値と一致する項目の値を返しますが、XLOOKUP関数のように下方からの検索や左方向の検索はできません。

注意事項

XLOOKUP関数はExcelの最新バージョンでのみ利用可能です。旧バージョンのExcelには含まれていませんので、その点を注意してください。

FAQセクション

XLOOKUP関数はVLOOKUP関数の代わりとして常に使用できますか？: 基本的には代わりとして使用できますが、旧バージョンのExcelでは使用できないため、その点を考慮する必要があります。

XLOOKUP関数の詳細解説(Excel)