Microsoft 365 の Excel に追加された XLOOKUP 関数について、今回は ”はじめの一歩” として VLOOKUP 関数を使っているユーザーの方向けに違いをご紹介します。
(XLOOKUP 関数は Excel 2016 や Excel 2019 では利用できないのでご注意を)
自分ではすぐに使い始めることがなかったとしても、一緒にお仕事をされるメンバーの誰かが XLOOKUP 関数による数式を使い始めているかもしれません。実務で直面にする前に、「こんなものがあるんだなー」と知っておくとよいと思います。
数式に必要な材料 (引数) の指定について
VLOOKUP 関数と比較して、XLOOKUP 関数を使った数式にはどんな材料が必要か、どのように指定していくのかを確認してみましょう。
VLOOKUP 関数の構文はこちら↓
=VLOOKUP (検索値 , 範囲 , 列番号 , [検索方法])
下図では、セル E2 の値を検索値、A2 から C11 を範囲、返してほしい値の格納されている列として左から 2 列目 ([名前] 列)、完全一致 (FALSE) の検索方法を指定しています。
VLOOKUP 関数は指定した範囲の左端列でしか探してくれないので、キーワードとしたい値は表の左端列に準備しないといけません。
XLOOKUP 関数の構文はこちら↓
=XLOOKUP(検索値 , 検索範囲 , 戻り範囲 , [見つからない場合] , [一致モード] , [検索モード])
検索値は VLOOKUP 関数と同じ、キーワードとしたい値 (が格納されているセル) を指定します。
検索範囲はその名のとおり、検索値に指定した値があるかなー？と探す範囲です。VLOOKUP 関数の場合は有無をいわさず範囲の左端列ですが、XLOOKUP 関数の場合は「ここだよ」と自分で指定します。
戻り範囲は結果として返したい値の含まれる範囲です。VLOOKUP 関数の場合は「(範囲の左から) 2 (列目)」のように指定していましたが、列番号ではなくセル範囲を指定できます。
最低限、この 3 つの引数が指定できれば数式で処理されます。残りの 3 つも引数名をみたら想像がつくかもしれませんが、分けて書きたいので今回は割愛します。
ということで、
図では、セル E2 の値を検索値、A2 から A11 を検索範囲、返してほしい値の格納されている列として B2 から B11 を指定しています。
なお、XLOOKUP 関数では今回のように最低限の 3 つの引数だけを指定していて、特に指定をしなければ完全一致で検索しますので、たとえば下図の「15」のような完全に一致する値が検索範囲に存在しない場合は #N/A エラーが返ります。
表の左端以外で検索する
前述したとおり、XLOOKUP 関数は検索範囲を指定できるため、VLOOKUP 関数のように検索値を含む列を表の左端列に配置する、といった準備をしなくても (関数に表の構造を合わせなくても) 処理できます。
下図では、セル E2 の値を検索値、C2 から C11 を検索範囲、返してほしい値の格納されている列として A2 から A11 を指定しています。
これまで、「表の右のほうにある列で検索して、左のほうにある列の値を返す数式を作りたい」という理由で INDEX 関数と MATCＨ 関数を組み合わせて使っていたユーザーは、これからは XLOOKUP 関数で対応することも可能、ということですね。
スピルありきで XLOOKUP 関数を使う
下図では、VLOOKUP 関数を使って、セル E2 を検索値とした数式をセル F2 とセル G2 に作成しています。
やり方はめちゃくちゃたくさんあるけれど、まずはセル F2 に数式を作って、セル G2 にコピーして、おかしなところだけ直そう、という対応をする方は少なくないと思います。
下図では、列番号だけが異なる 2 つの数式をそれぞれのセルに用意しています。
XLOOKUP 関数はスピルと組み合わせて使うことができるので、最初に数式を作ったセルをコピーして必要な修正をする、とか、参照する範囲にズレが生じないように絶対参照にする、といったことをしなくても同じ結果を得られます。(スピルについては こちら や こちら をご覧ください)
下図のセル F2 に数式を作成しています。
セル E2 の値を検索値、C2 から C11 を検索範囲、返してほしい値の格納されている列として B2 から C11 (← C11 ですよ、2 列ですよ、ここ大事) を指定しています。
数式を作成して [Enter] キーなどで確定すると、スピルが機能して、数式を作成したセル F2 はもちろんのこと、セル G2 にも結果が表示されます。
まずは XLOOKUP 関数の概要からご紹介しました。
VLOOKUP 関数と同じくらい浸透するまでにはまだ時間はかかると思いますが、冒頭でも書いたように徐々に使う人が出てきて、お仕事で目にする日が来るかもしれませんし、自分でも使わなければならない日もくるでしょう。
そんなときのためにさわりくらいは知っておきたいですね。
石田 かのこ
