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

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

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

※ 公式サイトでの解説 : XLOOKUP 関数

(2021 年 10 月 01 日公開 / 2022 年 03 月 03 日更新)

XLOOKUP関数の定義

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

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

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

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

XLOOKUP関数の定義(1)

XLOOKUP関数の定義(2)

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

XLOOKUP関数の定義(3)

XLOOKUP関数の定義(4)

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

XLOOKUP関数の定義(5)

XLOOKUP関数の定義(6)

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

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

XLOOKUP関数の定義(7)

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

XLOOKUP関数の定義(8)

一致モードを指定する

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

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

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

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

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

XLOOKUP関数の定義(9)

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

XLOOKUP関数の定義(10)

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

XLOOKUP関数の定義(11)

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

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

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

XLOOKUP関数の定義(12)

検索モードを指定する

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

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

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

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

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

XLOOKUP関数の定義(13)

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

XLOOKUP関数の定義(14)

XLOOKUP関数の使い方

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

XLOOKUP関数の使い方(1)

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

=XLOOKUP(B9,B3:B7,C3:D7,"--")

XLOOKUP関数の使い方(2)

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

XLOOKUP関数の使い方(3)

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

XLOOKUP関数の使い方(4)

XLOOKUP関数の使い方(5)

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

XLOOKUP関数の使い方(5)

関数の挿入を使ってXLOOKUP関数を入力する

XLOOKUP 関数を入力する場合に関数の挿入を使って行う方法を試してみます。関数を挿入するセルをクリックして選択したあとで、関数の挿入をクリックします。

関数の挿入を使ってXLOOKUP関数を入力する(1)

「関数の挿入」ダイアログが表示されたら関数名のところで「XLOOKUP」をクリックしてください。そのあとで「OK」をクリックしてください。

関数の挿入を使ってXLOOKUP関数を入力する(2)

「関数の引数」ダイアログが表示されたら、 1 番目の引数に検索値を入力するセルを指定します。 1 番目の引数を入力するテキストボックスをクリックして選択してから引数に入力するセルを Excel 上でクリックして下さい。

関数の挿入を使ってXLOOKUP関数を入力する(3)

関数の挿入を使ってXLOOKUP関数を入力する(4)

ダイアログの 1 番目の引数のところに先ほどクリックしたセルが表示されます。

関数の挿入を使ってXLOOKUP関数を入力する(5)

続いて 2 番目の引数に検索範囲となる配列またはセル範囲を指定します。 2 番目の引数を入力するテキストボックスをクリックして選択してから引数に入力するセル範囲を Excel 上で選択して下さい。

関数の挿入を使ってXLOOKUP関数を入力する(6)

関数の挿入を使ってXLOOKUP関数を入力する(7)

ダイアログの 2 番目の引数のところに先ほど選択したセル範囲が表示されます。

関数の挿入を使ってXLOOKUP関数を入力する(8)

続いて 3 番目の引数に戻り範囲となる配列またはセル範囲を指定します。 3 番目の引数を入力するテキストボックスをクリックして選択してから引数に入力するセル範囲を Excel 上で選択して下さい。

関数の挿入を使ってXLOOKUP関数を入力する(9)

関数の挿入を使ってXLOOKUP関数を入力する(10)

ダイアログの 3 番目の引数のところに先ほど選択したセル範囲が表示されます。

関数の挿入を使ってXLOOKUP関数を入力する(11)

今回 4 番目、 5 番目、 6 番目の引数は省略します。設定する場合はそれぞれのテキストボックスに直接値を入力するか、値が入力されたセルを指定してください。

引数の指定が終わりましたら最後に「OK」をクリックしてください。最初に選択したセルに XLOOKUP 関数が入力されます。

関数の挿入を使ってXLOOKUP関数を入力する(12)

検索値が入力されていないので #N/A が表示されます。それでは B9 セルに検索値を入力してください。 XLOOKUP 関数を入力したセルに対応する項目の値がが表示されます。

関数の挿入を使ってXLOOKUP関数を入力する(13)

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

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

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

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

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

=XLOOKUP(検索値,セル範囲1&セル範囲2,戻り範囲)

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

=XLOOKUP(C10,B3:B8&C3:C8,D3:D8,"None")

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

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

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

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

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

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

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

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

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

=XLOOKUP(セル1&セル2,セル範囲1&セル範囲2,戻り範囲)

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

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

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

=XLOOKUP(C10&C11,B3:B8&C3:C8,D3:D8,"None")

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

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

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

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

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

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

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

XLOOKUP関数の便利な利用方法

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

-- --

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

( Written by Tatsuo Ikura )

広告
Profile
profile_img

著者 / TATSUO IKURA

初心者~中級者の方を対象としたプログラミング方法や開発環境の構築の解説を行うサイトの運営を行っています。