Excel XLOOKUP関数の使い方｜みらんく・ぷらす

XLOOKUP関数はVLOOKUP関数の上位互換として登場した関数です。
選択範囲の左側を検索し右側のデータしか表示できませんした。
INDEX関数+MATCH関数で代用したりしていましたが、
この関数の登場で検索参照がラクになりました。

そんなXLOOKUP関数を見ていきます。

https://milank.jp/excel-functions-vlookup/

この関数はExcel 365＆2019で使用可能です

使用例はExcel for 365 for Macによるものです

も　く　じ

XLOOKUP関数の機能
使用例
検索範囲と戻り範囲がズレている場合
スピルについて

XLOOKUP関数の機能

関数の構文

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

・検索値
検索する値の入ったセルを指定(直接入力でも可)。

・検索範囲
検索値を検索する対象となる範囲を指定。

・戻り範囲
検索結果を返すデータ（戻り値）の範囲を指定。

・[見つからない場合]
検索対象が見つからなかった場合の動作を指定。
省略可能

・[一致モード]
検索一致の種類を指定。省略すると0（完全一致）扱い。

・[検索モード]
検索順序を指定。省略すると1（先頭から検索）扱い。

見つからない場合について

検索値がなかった場合の処理を指定できます。
「”見つかりませんでした”」と入力すればそのように表示され、
別の関数を入れ子にして別の検索結果を表示するなど行える。
指定がなく検索結果がなければ「#N/A」と表示される。

一致モードについて

一致モードで入力できる値と動作については下のようになります。
入力を省略すると「完全一致のみの検索」になります。

入力値	動作内容
0	完全一致のみを検索
-1	完全一致がなければ検索値未満の最大値
1	完全一致がなければ検索値超えの最小値
2	ワイルドカードを指定した検索

検索モードについて

検索モードで入力できる値と動作については下のようになります。
入力を省略すると「先頭から末尾へ向かっての検索」になります。

入力値	検索方法
1	先頭から末尾へ検索
-1	末尾から先頭へ検索
2	バイナリ検索（昇順で並替えが必要）
-2	バイナリ検索（降順で並替えが必要）

バイナリ検索とは？
2分探索法と呼ばれる高速な検索手法
最初は全体の中央値と比較。検索値が中央値より小さければ、さらに前半の中央値を調べる……という具合に一致したデータがあるか該当がなくなるまで繰り返す検索方法。検索対象が多い場合、検索時間を短くできる。

使用例

検索内容を確認

検索値を社員IDから検索。
対象の名前を表示させるようにXLOOKUP関数を使用してみます。

関数を入力する

実際に入力した関数はこちら
=XLOOKUP(I5,社員リスト[社員ID],社員リスト[氏名],”ないよ”,0,2)

①検索値
検索値を入力するセル”I5”を指定
②検索範囲
検索する範囲であるテーブル”社員リスト”の”社員ID”の欄を選択
③戻り範囲
検索結果として返すテーブル”社員リスト”の”氏名”の欄を選択
④見つからない場合
「ないよ」と表示させるため「”ないよ”」と入力
（文字を表示させる場合は””で囲む）
⑤一致モード
完全一致のみを対象とするため「0」を入力
⑥検索モード
検索範囲は昇順に並んでいるため、
昇順のバイナリ検索である「2」を入力