こんにちは。
@OfficeTAKUです。
ご存知のことと思いますが、Excelのバージョンが上がる度にExcelの関数も増えています。
今回は Excel 365 、Excel 2019 から追加された関数、XLOOKUP関数についてです。
XLOOKUP関数かなり進化しています。
まずは、従来のVLOOKUP関数の代わりとして使う方法。
今回は近似値検索の場合です。
サブスクリプション：Microsoft 365 for business
- Microsoft Excel for Mac バージョン16.46
- Microsoft Excel for Microsoft 365 MSO 16.0
目次
XLOOKUP関数
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
(大かっこ[]の引数はオプション・省略可)
| 引数 | 説明 |
|---|---|
| 検索値 | 従来と同じ検索したい値を設定 |
| 検索範囲 | 検索する範囲or配列 |
| 戻り範囲 | 結果として返したい範囲or配列 |
| [見つからない場合] | 検索値に一致するものが見つからない場合に表示したい文字列を入力 |
| [一致モード] | 0 – 完全一致：これがデフォルト（既定値）。 -1 – 近似値：見つからない場合は、検索値未満の最大値が返されます（VLOOKUP関数で近似値検索の時に指定したTRUEに該当)。 1 – 近似値：見つからない場合は、検索値より大きい最小値が返されます。 2 – *、?、および 〜 が特別な意味を持つワイルドカードの一致。 |
| 1 – 先頭の項目から検索を実行。これがデフォルト（既定値）。 -1 – 末尾の項目から逆方向に検索を実行。 2 – 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行。並べ替えられていない場合、無効な結果が返されます。 -2 – 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行。並べ替えられていない場合、無効な結果が返されます。 |
XLOOKUP関数でコードを検索し商品名・単価を表示させる
下図のようなBMI（Body Mass Index）判定表を作成してみましょう。
D列のBMIの値を、右の「肥満度分類（日本肥満学会）」表（セル範囲 G2:H7）から検索し判定を、E列の判定欄に表示します。
【数式：セルE2】
=XLOOKUP(D2,$G$2:$G$5,$H$2:$H$5,””,-1)
関数の引数ダイアログボックス（イメージ）：Windows
関数の引数ダイアログボックスの入力例
注意：画像合成してあります。実際はダイアログボックス内でスクロールして下さい。
数式パレット作業ウインドウ（イメージ）：Mac
数式パレットの入力例
注意：画像合成してあります。実際は数式パレット内でスクロールして下さい。
ポイントとしては、
- 検索範囲
肥満度分類表のBMI セル範囲G2:G5(コピーのために絶対参照) - 戻り範囲
肥満度分類表の判定 セル範囲H2:H7 - [見つからない場合]
コードが未入力の時には空白を表示したいので “” (ダブルコーテーション)を2つ入れています。
場合に応じて、「該当なし」とか「見つかりません」などという風にメッセージを入力しても構いません。
VLOOKUP関数であれば、IF関数を利用したりしていましたが、それが必要なくなりました。便利です。
省略するとエラー[#N/A]が表示されてしまいますので注意が必要です。 - [一致モード]
[-1]を指定します。従来のVLOOKUP関数での[TRUE]と同様の働き - [検索モード]
省略、あるいは[1]を指定。
完成例
XLOOKUP関数とVLOOKUP関数での数式比較
XLOOKUP関数で作成した数式と従来のVLOOKUP関数を利用した数式を比較してみましょう。
BMIが空白の場合など結果がエラーとなる場合は空白を返す、という設定で、VLOOKUP関数の場合は、IF関数とISERROR関数を利用しています。
=XLOOKUP(D2,$G$2:$G$5,$H$2:$H$5,””,-1)
=IF(ISERROR(=VLOOKUP(D2,$G$2:$H$7,2,TRUE)),””,VLOOKUP(D2,$G$2:$H$7,2,TRUE))
まとめ
Excel 365,2019で追加された XLOOKUP 関数
従来のVLOOKUP関数の代わりに使うことができます。
もちろんVLOOKUP関数がなくなったわけではありませんので、今回の場合はあえて使うことはありません。
しかし、より幅広い使い方のできる関数ですので、慣れるためにも利用していくとよいと思います。
この関数も下位バージョンでは利用できません。
会社や学校で、Excel2016以前が使用されている状況、ファイルをやり取りする必要がある環境では使わない方がいいですね。
