ひとつで2役、XLOOKUP関数 Excel2021 Microsoft 365

XLOOKUP関数は、VLOOKUP関数を使う上で、不便だったことを解決した新しい関数です。

ひとつで2役、XLOOKUP関数 関数 07b回

XLOOKUP関数とは

さて今度は VLOOKUP 関数の代わりに Xlookup 関数を使って売上金額を出してみたいと思います。
XLOOKUP関数は縦方向にデータを検索するVLOOKUP関数と、横方向にデータを検索するHLOOKUP関数の両方を備える関数です。
さらに、スピル対応の関数でもあります。スピルはVLOOKUP関数内でも利用することはできましたが、どこが違うのでしょうか?
XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。
一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。 *省略した場合、XLOOKUPは検索範囲に空白のセルを返します。
(サンプルファイルは、こちらから サンプルデータ


XLOOKUP関数の構文

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
検索値 参照する値、セル
検索範囲 検索する配列または範囲
戻り範囲 返す配列または範囲
見つからない場合 見つからない場合は、指定したテキストを返す。

 引数、[見つからない場合],[一致モード],[検索モード]は省略可能です。 

一致モード 一致の種類を指定する。
0 (既定)完全一致。 見つからない場合は、#N/A を返す。
-1 完全一致。 見つからない場合は、次の小さなアイテムを返す。
1 完全一致。 見つからない場合は、次の大きなアイテムを返す。
2 *、?、および ~ ワイルドカードを使う。
検索モード 検索モードを指定
1 (既定)先頭の項目から検索を実行。
-1 末尾の項目から逆方向に検索を実行。
2 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行
並べ替えられていない場合、無効な結果を返す。
-2 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行
並べ替えられていない場合、無効な結果を返す。

XLOOKUP関数で表引き

  1. G3セルに XLOOKUP関数の数式を入力します。まず引数、検索値は、 E3から E10を範囲指定して=XLOOKUP(E3:E10としてやります。
    すると自動的にスピルを感知してE3#と入力されているのが確認できます。
    ひとつで2役、XLOOKUP関数、タイトル01
  2. 第2引数、検索範囲は、I2からI10ですが、範囲を選択すると、I3#と入力されます。自動的にスピルを感知します。
    ひとつで2役、XLOOKUP関数、タイトル02
  3. 第3引数、戻り値は、単価J3:J9です。この列は数値が入力されているセルですので、スピルは使われていません。以上で単価を表引きする数式は入力できました。
    XLOOKUP関数は、スピルに対応した関数なので、手入力で修正しなくても、スピル対応の数式になります。

    ひとつで2役、XLOOKUP関数、タイトル03
  4. 最後に、売上個数を乗算して、売上金額を出す数式は完成です。 =XLOOKUP(E3#,I3#,J3:J9)*F3#

    ひとつで2役、XLOOKUP関数、タイトル04
  5. G3セルにだけ、数式が入力されており、それ以外は、スピルしているので、グレーアウトで薄く表示されています。

    ひとつで2役、XLOOKUP関数、タイトル05