XLOOKUP関数とは？
XLOOKUP関数は表の中から指定した値を検索して返してくれる関数になります。
同じような関数でVLOOKUP関数がありますが、XLOOKUP関数は2020年にMicrosoft365のアップデートの際に追加されたVLOOKUP関数の後継関数となります。
そのため、Microsoft365・Excel2021以降のバージョンのみ使用可能な関数となり、Excel2019以前のExcelでは使用できません。
この機会にVLOOKUP関数との違いやXLOOKUP関数の使い方をしっかり覚えてマスターしていきましょう！
練習用エクセルファイルをご用意したので、必要な方は下記よりダウンロードしてくださいね。
VLOOKUP関数との違い
表の中から指定した値を検索して返してくれる関数の代表的な関数としてVLOOKUP関数があります。
今回ご紹介するXLOOKUP関数とでは細かい違いもありますが、一番大きな違いは「検索値の左側にあっても値を返してくれる」ことです。
これまでフォーマットの都合上、どうしても返してほしい値が検索値の左側にある場合、INDEX関数とMATCH関数を一緒に組むことで対応可能でしたが、XLOOKUP関数は検索範囲と戻り範囲を別々に指定するので「検索値が一番左にないといけない」という大前提がないため、どの配列であっても自由自在に抽出することが可能になりました。
（※INDEX関数とMATCH関数の記事は準備中です）
1つの関数で自由自在に抽出することが可能になったので
関数もスッキリして、うれしいですね！
基本的な動き
では練習用ファイル、基本1シートを使用してXLOOKUP関数の基本的な動きを見ていきましょう。
C2セルに = と入力し、続けてXLOOKUP関数を入力します。
途中まで入力すると候補がXLOOKUPだけに絞れるので、XLOOKUPを選択したら Tab キーで使用する関数を確定させます。
まず最初に第1引数を入力します。
第1引数は検索値で、検索したい値が入っているセルを指定します。
今回でいうとB列の商品名を検索したいので、B2セルを指定しましょう。
第1引数を入力したらカンマ , を打ち、続けて第2引数を入力します。
第2引数は検索範囲で、第1引数で指定した検索値を探す場所になります。
ここではB列の商品名をE1～G7の表から探してくるので、第2引数にはE2～E7セルを指定します。
この時、絶対参照にするのを忘れないようにしましょう。
第2引数を入れたら、カンマ , を打って第3引数を入力します。
第3引数は戻り範囲で、第2引数で検索した結果、返す値が記載されている場所になります。
今回は商品番号が返ってきてほしいので、第3引数はF2～F7セルを指定します。
ここでも絶対参照にするのを忘れないようにしましょう。
必須引数は第3引数までになるので、一旦ここで Enter を押してXLOOKUP関数を完成させましょう。
問題なく商品番号が返ってきたので、オートフィルでXLOOKUP関数を最終行まで反映させましょう。
このように、XLOOKUP関数では、検索範囲と戻り範囲を別々に指定することで、VLOOKUP関数ではできなかった「検索値の左側の値を返す」ことが可能になったんですね！
XLOOKUP関数はINDEX関数とMATCH関数を一緒に組む時と少し似ていますね。
=XLOOKUP(検索値,検索範囲,戻り範囲)
=XLOOKUP(B2,$E$2:$E$7,$F$2:$F$7)
スピル機能を使った入力方法
Microsoft365・Excel2021以降のバージョンには「スピル機能」というものがあります。
このスピル機能を使うと、もっと便利にXLOOKUP関数を使用することができるので一緒に覚えておきましょう！
- 数式を入力したセルに隣接したセルにも、自動的に結果が反映される機能
- スピル機能を使うことで、オートフィルで数式をコピー反映する必要がなくなる
- すべての関数がスピル機能に対応しているわけでない
結果を縦に自動反映させたい場合
では練習用ファイルの基本2シートを使用して、スピル機能を使用してXLOOKUP関数の結果を縦に自動反映させる方法を見ていきましょう。
C2セルにXLOOKUP関数を入れて、第1引数を入力します。
第1引数は検索値なので、通常は「B2」と入力しますが、スピル機能を使用する場合はC3セル以下のセルにも同じ数式結果を反映させたいので、検索値が入っている範囲すべてを指定します。
今回は検索値はB2～B40セルに入っているので、第1引数は「B2:B40」となります。
オートフィルはしないので、特に絶対参照は不要です。
第1引数を指定したらカンマ , を打って第2引数を入力します。
第2引数は検索範囲なので、検索値が入っているG2～G7セルを選択します。
ここでも、オートフィルはしないので、特に絶対参照は不要です。
第2引数を指定したらカンマ , を打って第3引数を入力します。
第3引数は戻り範囲なので、戻り値が入っているF2～F7セルを選択します。
最後に Enter を押してXLOOKUP関数を完成させましょう。
すると数式を入力したC2セルだけではなく、C3セル以下にもXLOOKUP関数の結果が反映されました。
スピル機能を使用する場合、範囲開始位置は必ず同じである必要があります。
例えば検索値を「B2:B40」としたのに、検索値範囲を「G1:G7」などとするとエラーになるので注意してください。
=XLOOKUP(検索値すべて,検索値範囲,戻り値範囲)
=XLOOKUP(B2:B40,G2:G7,F2:F7)
結果を横に自動反映させたい場合
では練習用ファイルの基本3シートを使用して、スピル機能を使用してXLOOKUP関数の結果を横に自動反映させる方法を見ていきましょう。
C2セルにXLOOKUP関数を入れて、第1引数を入力します。
第1引数は検索値なので、ここでは「B2」を指定します。
第1引数を指定したらカンマ , を打って第2引数を入力します。
第2引数は検索範囲なので、今回はF2～F7セルを指定します。
あとで数式をコピーするので、絶対参照にしておきましょう。
第2引数を指定したらカンマ , を打って第3引数を入力します。
第3引数は戻り範囲ですが、今回は商品番号と単価の列が元データも戻り範囲も同じ並び順で隣同士に並んでいるので、まとめて返すよう入力したいです。
ここでは「G2:H7」を指定し、絶対参照にします。
最後に Enter を押してXLOOKUP関数を完成させましょう。
すると数式を入力したC2セルだけではなく、D3セルにもXLOOKUP関数の結果が反映されました。
スピル機能を使っているとオートフィルが使えないので、C2～C40セルを選択して Ctrl + D で数式をコピーしてあげましょう。
スピル機能によって、D2にも結果が反映されていますが
実際に数式が入っているのはC2セルだけなのでコピーするのはC列だけでOK！
=XLOOKUP(検索値,検索範囲,戻り範囲のすべて）※隣接している場合
=XLOOKUP(B2,$F$2:$F$7,$G$2:$H$7)
スピル機能の記事は準備中です。
動画がありますので、スピル機能の詳細は下記の動画をご確認ください
まとめ
XLOOKUP関数の基本の動きについてご紹介しました。
これまでVLOOKUP関数ではできなかった「検索値の左側」をXLOOKUP関数ひとつで検索することができるので、とっても助かります！
今回は基本編なので、必須の引数だけをご紹介しましたが、別途 応用編として任意の引数の使い方もご紹介します。まずはXLOOKUPの基本をしっかり覚えて、応用編に進みましょう！
動画でもご紹介していますので、動画を見ながら操作を確認したい方は下記からどうぞ！
励みになります！高評価とチャンネル登録よろしくおねがいいたします