XLOOKUP関数の使い方

XLOOKUP関数は3652021から使用可能となった新しい関数です。

VLOOKUP関数の上位互換といっても良い程使い勝手がよく、もしあなたの職場や自宅でのPC環境が使用可能であれば、今後はこちらの関数を使用する方が何かと便利です。

関数構文

XLOOKUP関数の仕様です。

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

引数	説明
①検索値	検索する値（または範囲、配列）を指定します。
②検索範囲	検索対象となる範囲（または配列）を指定します。
③戻り範囲	返す対象となる範囲（または配列）を指定します。
④見つからない場合	値が見つからなかった場合に表示される文字列を指定します。 省略した場合は「#N/A」エラーが表示されます。
⑤一致モード	一致の種類を指定します。省略した場合は「0」と同じです。 0：完全一致。 見つからない場合は、「④見つからない場合」に指定した文字列が表示されます。 -1：完全一致。 見つからない場合は、次の小さなアイテムが返されます。 1：完全一致。 見つからない場合は、次の大きなアイテムが返されます。 2：*、?、および 〜 が特別な意味を持つワイルドカードの一致。
⑥検索モード	検索モードを指定します。省略した場合は「1」と同じです。 1：先頭の項目から検索を実行します。 -1：末尾の項目から逆方向に検索を実行します。 2：昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。 -2：降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。

一つずつ引数の使い方を解説していきます。

①検索値

「商品ID=S002」に紐づく商品名「カツ丼」を取得したいので、第1引数の検索値に「B13」セルを指定します。

検索値を範囲にすると、結果も配列（範囲）で返ってきます。

その結果、スピル機能により一度に取得したい値が求められます。

②検索範囲

検索対象となる行、または列を指定します。

今回は商品IDなので、「A3:A5」を検索範囲として指定します。

③戻り範囲

返す値が含まれている範囲を指定します。

この時、戻り範囲に複数列を指定すると、返ってくる結果も複数列になります。

上図では「C2:E6（5行3列）」を指定しているので、結果も3列返ってきており、スピル機能により3列分が一度に表示されています。

検索範囲よりも左側の値を戻り範囲に指定出来るようになっています。

これは=VLOOKUP()関数では出来なかった事です。

=INDEX()関数と=MATCH()関数を組み合わせて使えば、同じ結果が得られますが、=XLOOKUP()関数の方が直感的で理解しやすいかと思われます。

④見つからない場合

値が見つからなかった場合に表示される文字列を指定します。

省略した場合は「#N/A」エラー（Not Applicable：該当なし）が表示されます。

⑤一致モード

一致モードは、見つかった場合の挙動は変わりませんが、見つからなかった場合の挙動に違いがあります。

一致モード「0」

完全に一致した結果のみが正常に表示されます。

見つからなかった場合は、「④見つからない場合」に指定した文字列が表示されます。

上記は省略しているので、「#N/A」エラーが表示されています。

一致モード「-1」

見つからなかった場合、その値を超えない次の小さな値を返します。

これは=VLOOKUP()関数で、検索方法にTRUEを指定した「近似一致」と同様の挙動になります。

一致モード「1」

見つからなかった場合、次の大きな値を返します。

先程の一致モード「-1」の時、「ブヒコ」の成績が「C」であったのに対し、こちらは「B」になっています。

また、「ピヨコ」の成績もエラーから「E」に変わっています。

一致モード「-1」で返される値よりも一つ上の値が返される挙動になります。

一致モード「2」

ワイルドカードを使用して「あいまい検索」をします。

ワイルドカードは「①検索値」で使用します。

「*」(半角アスタリスク)は任意の文字、「?」は任意の一字を表します。

「カツ丼」は検索値「*丼」に対して一致しますが、「?丼」に対しては一致しません。

「カツ」が2文字であるため、「?」を用いて一致させるには、「??丼」と指定します。

⑥検索モード

検索モード「1」

「②検索範囲」で指定した範囲を先頭から検索し、最初に一致した値を返します。

省略した場合もこの動作と同じになります。

検索モード「-1」

「②検索範囲」で指定した範囲を末尾から検索し、最初に一致した値を返します。

検索モード「1」と逆の挙動で検索します。

検索モード「2」

二分探索（バイナリサーチ）アルゴリズムを用いて検索を行います。

「②検索範囲」が昇順でソートされている必要がありますが、検索速度は非常に高速です。

=XLOOKUP()関数を使ってデータを何万件も処理する必要がある時に、このモードにするとExcelの動作が軽くなる事があります。

検索モード「-2」

こちらも検索モード「2」と同様に二分探索で検索を行います。

降順必須、検索速度は高速です。

検索条件を複数指定

検索条件を複数指定したい場合は、条件を任意の文字【 _ 】でつなげて、「①検索値」と「②検索範囲」に指定します。

任意の文字は無くても良い場合が多いとは思いますが、上図の様に「広」＆「島豚肉」と「広島」＆「豚肉」の区別がつかなくなるパターンが存在した場合に不都合が出ますので、なるべく付けることを推奨します。