XLOOKUP関数：指定の範囲を検索し対応する位置にある値を取得する

XLOOKUP 関数は Excel で用意されている関数の一つで、指定した範囲を縦または横方向に検索し、項目が見つかった場合は別に指定した範囲の中の対応する位置にある項目の値を取得します。 XLOOKUP 関数は Microsoft 365 以降で利用可能です。ここでは Excel における XLOOKUP 関数の使い方について解説します。

※ Excel の対応バージョン ： 365 web 2021

(Last modified: 2024-05-25)

XLOOKUP関数の定義

XLOOKUP 関数は引数に指定した範囲の中を縦または横法にに検索し、見つかった場合は別に指定した範囲の中の対応する項目の値を取得します。

1 番目の引数に検索する値を指定します。 2 番目の引数に検索を行う配列またはセル範囲を指定します。 3 番目の引数に戻り値の配列またはセル範囲を指定します。

1 番目の引数に指定した値を 2 番目の引数に指定した範囲で検索し、見つかった場合は 3 番目の引数に指定した範囲から対応する位置にある項目の値を返します。対応する位置というのは、例えば縦方向に検索した場合は見つかった項目が上から何番目かを調べ、戻り値範囲の中で上または左から同じ番目にある項目を返します。

XLOOKUP 関数では戻り範囲として複数の行や列が含まれる範囲を指定することができます。この場合、検索範囲で値が見つかった場合、対応する複数の項目をまとめて取得することができます。

XLOOKUP 関数では検索範囲に指定する範囲の向きで、縦方向だけでなく横方向に自動的に検索し、対応する項目の値を取得することができます。

検索値が見つからなかった場合

4 番目の引数は省略可能な引数で、検索値が検索範囲で見つからなかった場合に #N/A ではなく別の文字列を返す場合に設定します。引数を省略した場合、検索値が検索範囲内で見つからないと #N/A と表示されます。

4 番目の引数に値を設定した場合、検索値が検索範囲内で見つからないと設定した値が返されます。

一致モードを指定する

5 番目は省略可能な引数で、一致モードを指定します。設定可能な値とそのときのモードは次のようになっています。

一致モード	説明
0	完全一致。見つからない場合は #N/A
-1	完全一致。見つからない場合は、検索値よりも小さな値の中で最大の項目を一致したものとみなす
1	完全一致。見つからない場合は、検索値よりも大きな値の中で最小の項目を一致したものとみなす
2	ワイルドカードを使った一致を行います

0 を指定したときがデフォルトの動作です。検索値と完全に一致するものを探し、見つからなかった場合は #N/A エラーとなります。一致モードの値を省略した場合は 0 が指定されたものとして扱われます。

-1 を指定した場合、検索値と完全に一致する値が見つからなかったら検索値よりも小さくて最大の項目を一致したものとみなします。 1 を指定した場合、検索値と完全に一致する値が見つからなかったら検索値よりも大きくて最小の項目を一致したものとみなします。

例えば一致モードで 0 を指定するか省略した場合、検索値として 2500 を指定すると検索範囲に見つからないため #N/A となります。

一致モードで -1 を指定すると、検索値が見つからなかった場合に検索値よりも小さくて最大の値を一致したものとみなすため、検索値として 2500 を指定すると 2000 の項目が一致したものとみなされます。

一致モードで 1 を指定すると、検索値が見つからなかった場合に検索値よりも大きくて最小の値を一致したものとみなすため、検索値として 2500 を指定すると 3000 の項目が一致したものとみなされます。

2 を指定した場合は完全一致ではなくワイルドカードを使った検索が可能です。使用可能なワイルドカードは次の 3 つです。

ワイルドカード	説明
?	任意の一文字
*	任意の数の文字
~	文字として?や*や~を使用したい場合に~?のように直前に記述する

下記では一致モードに 2 を指定した上で、検索値としてワイルドカードを使って "A*3" と指定しています。この場合 "A" で始まり任意の文字が任意の個数続いたあとで最後に "3" で終わる値と一致します。

検索モードを指定する

5 番目は省略可能な引数で、検索モードを指定します。設定可能な値とそのときのモードは次のようになっています。

一致モード	説明
1	先頭の項目から検索する
-1	末尾の項目から検索する
2	昇順で並べ替えられた検索範囲に対してバイナリ検索を行う
-2	降順で並べ替えられた検索範囲に対してバイナリ検索を行う

1 を指定したときがデフォルトの動作です。上から下、または左から右へ順に先頭から末尾へと検索します。 -1 を指定した場合は逆に下から上、または右から左へ末尾から先頭へ検索します。検索モードの値を省略した場合は 1 が指定されたものとして扱われます。

2 または -2 を指定した場合はバイナリ検索を行います。高速で検索可能ですが、検索範囲に含まれる値が 2 の場合は昇順、 -2 の場合は降順に並び替えられていないと正しい結果は得ることができませんのでご注意ください。

例えば検索モードで 1 を指定するか省略した場合、検索範囲を先頭から末尾へ検索するため "炭酸飲料" で検索すると上から 2 つ目の項目が見つかります。

検索モードで -1 を指定すると検索範囲を末尾から先頭へ検索するため "炭酸飲料" で検索すると下から 2 つ目の項目が見つかります。

XLOOKUP関数の使い方

それでは実際に XLOOKUP 関数を使ってみます。 Excel のシートに対象の数値を次のように入力しました。

検索値を入力するセルとして B9 を使用します。そして検索結果を表示する C9 セルを選択したあとで次のように入力します。検索する値を指定する 1 番目の引数には B9 セル、検索範囲を指定する 2 番目の引数にはセル範囲 B3:B7 、値を取得する 3 番目の引数にはセル範囲 C3:D7 、見つからなかった時の文字列を指定する 4 番目の引数には文字列 "--" を指定しました。

Enter キーを押すと、 C9 セルには次のように表示されます。まだ検索値を入力していないため、見つからなかったときの表示が行われています。

では B9 セルに検索値として "A-03" を入力してみます。すると、 C9 セルおよび D9 セルに対応する項目の値が表示されました。

B9 セルに違う値を入力すると、対応する別の値が表示されます。

複数の列の値を結合したものを検索範囲として指定する

XLOOKUP 関数では検索範囲として複数の列の値を結合したものを検索範囲として指定することができます。次のシートを見てください。

検索値として "テーブルM" を指定したときに、 B 列の値と C 列の値を結合した値と一致するかどうかを調べることができます。 "テーブルM" で検索したのであれば B 列が "テーブル" で C 列が "M" になっている 4 行目のデータと一致します。

検索範囲として複数の列を結合した値を指定するには セル範囲1&セル範囲2 のように複数のセル範囲をアンパサンド(&)でつなげて検索範囲に指定します。

それでは実際に試してみます。取得した値段を表示する C11 セルに次のように入力しました。

それでは C10 セルに "テーブルM" と入力し、 Enter キーを押してください。

検索値と一致する行から値段の値を取得し C11 セルに表示しました。

同じように C10 セルに "椅子S" と入力すると、検索値と一致する行から値段の値を取得し C11 セルに表示しました。

検索値として複数のセルの値を結合した値を指定する

検索範囲だけでなく検索値についても複数のセルの値を結合したものを使用することができます。検索値として複数のセルを結合した値を指定するには セル1&セル2 のように複数のセルをアンパサンド(&)でつなげて検索値に指定します。

それでは実際に試してみます。次のようなシートを用意しました。

取得した値段を表示する C12 セルに次のように入力しました。

それでは C10 セルに "テーブル" 、 C11 セルに "L" と入力してから Enter キーを押してください。

検索値と一致する行から値段の値を取得し C12 セルに表示しました。

同じように C10 セルに "椅子" 、 C11 セルに "S" と入力すると、検索値と一致する行から値段の値を取得し C11 セルに表示しました。

XLOOKUP関数の便利な利用方法

XLOOKUP 関数の便利な利用方法を下記の記事で紹介しています。

-- --

Excel における XLOOKUP 関数の使い方について解説しました。