【Excel・エクセル】XLOOKUP関数とは？使い方やVLOOKUP関数との違いなど
XLOOKUP関数とは、なんぞ？VLOOKUPと違うの？
使えるなら使った方がいいよ、使い方を教えるね。
- 検索する関数は、VLOOKUP関数より簡単で便利なXLOOKUP関数！
- =XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)
- 見つからない場合以降は、省略可能
- ただし、対応バージョンのMicrosoft365、Excel2021じゃないと使えない
【Excel・エクセル】XLOOKUP関数とは？
検索の関数はVLOOKUP関数が有名ですが、検索値が必ず左にないといけなかったり、列番号を数える手間があったりと、面倒な部分もありますよね。
そこで使ってほしいのが、XLOOKUP関数！
VLOOKUP関数より引数の指定が簡単で、範囲も柔軟に指定できる、スーパー便利検索関数なんです。
さらにスピル可で、隣接する情報も一緒に抽出できるので効率がよく時短につながります。
スピルは範囲外の値も自動的に検索対象に含む機能。後で説明するね。
https://sakusaku-office.com/excel/post-17990/XLOOKUPの対応バージョン
え、じゃあもうVLOOKUP関数使わなくてもいいのでは？
XLOOKUP関数は新しい関数で、Microsoft365かExcel2021でないと使えません。
そう、Excel2019や2016では、XLOOKUP関数は使えないんです……
その時はVLOOKUP関数か、MATCH関数とINDEX関数を組み合わせて検索してね。
ちなみに読み方は、「エックスルックアップ関数」です。
VLOOKUP関数に比べ、柔軟性と効率性を兼ね備えたXLOOKUP関数。
さっそく、使い方を見てみましょう。
動画でもご紹介しています！
【Excel・エクセル】XLOOKUP関数の使い方（基本編）
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
| 検索値 | この値を |
| 検索範囲 | この範囲から探して |
| 戻り範囲 | その位置にある、この範囲の値を教えて |
| 見つからない場合 | もしなかったら、こうして（省略可） |
| 一致モード | 完全一致か、少し違ってもいいか （省略可） |
| 検索モード | 検索値をどうやって検索するか （省略可） |
え、待って、引数多くて難しそうじゃない？
大丈夫、基本は戻り範囲までしか使わないから。
セルF2の従業員NoからXLOOKUP関数を使って、入社日を抽出したいと思います。
※実際に操作できます。
いちいちスクロールするのは面倒なので、別タブか別ウィンドウで開くの推奨です！
=XLOOKUP(F2,A2:A7,D2:D7)で、求められるよ。説明するね。
ステップ1
セルG2に「＝XLOOKUP(」と入力してください。
ステップ2
次に検索値です。
今回はセルF2の従業員のNoをもとに検索するので、セルF2をクリックします。
そして「,（カンマ）」。
ステップ3
次に検索範囲。
これは検索値を含む列（または行）ですね。
今回は従業員のNoが書かれたセルA２からA7までをドラックして指定します。
「,（カンマも入れてね。
ステップ4
最後に戻り範囲、取り出したい値がある範囲を選択します。
今回は入社日の値を取り出したいので、セルD2からD7までをドラッグですね。
ここで一つ、注意があるよ！
それは、検索範囲と戻り範囲は必ず同じ大きさであること！
検索範囲がA2:A7で戻り範囲がD2:D8のように、範囲の大きさが違うとエラーになっちまうんです……
完成！
これで式は完成です。
エンターキーを押すと、無事、Noに該当する入社日が検索できました！
【Excel・エクセル】XLOOKUP関数とVLOOKUP関数の違い
VLOOKUP関数より簡単に同じようなことができると、お分かりいただけたと思います。
では、VLOOKUP関数じゃなくてXLOOKUP関数を使うメリットは、どこにあるんでしょうか？
VLOOKUP関数との違い1．あのスピルができる！
あのってどの？映画監督の？
違ぇよ。
スピルは簡単に言うと、該当のセルのついでに続きのセルの値も一緒に持ってきてくれる機能です。
ステップ１
スピりたいときは、戻り範囲を欲しい範囲まで選択します。
完成！
するとこの通り、名前と部署名が一度に取り出せました！
いちいちVLOOKUP関数をコピーして列番号を変えて……なんてせずに、一括で複数範囲を抽出できますね！
VLOOKUP関数との違い2．検索値は左になくてもいい
VLOOKUP関数は、検索範囲の左端に検索値がないといけませんでした。
が、検索範囲と戻り範囲をそれぞれ指定できるXLOOKUP関数では、左になくても大丈夫なんです。
検索範囲の左側にある列からも、値を検索できますよ。
VLOOKUP関数との違い3．列番号、検索方法が不要
VLOOKUP関数では、検索値の列から数えて何番目……と、列番号を数えて入れる必要がありました。
しかしXLOOKUP関数は、欲しい列を指定すればいいので、より直感的にできます。
これはマジうれしい。
また、VLOOKUP関数を使う時はほとんどが完全一致なのに、いちいちFALSEとか０とか検索方法を指定するのもダルいですよね笑
XLOOKUP関数では、省略で完全一致になるので、その手間も省けます！
VLOOKUP関数との違い4．見つからない場合が指定できる
VLOOKUP関数で検索値が範囲にない場合、エラーになってしまいます。
エラーを回避するには、IF関数やIFERROR関数を使わないといけません。
XLOOKUP関数では、引数で見つからない場合が指定できます。
IFERROR関数などを使わなくても、いなければ空白、なんてここと簡単にできるんです。
そのほかXLOOKUP関数では、「一致モード」と「検索モード」が指定できます。
【Excel・エクセル】XLOOKUP関数の検索モード・一致モードとは
あれ、もう抽出できたけど、一致モードと検索モードって何だったの？
基本は省略していいんだけど、どういう意味があるか見てみようか。
XLOOKUP関数の一致モードとは
一致モードは、VLOOKUP関数の検索方法のようなものです。
省略、または０にすると「完全一致」、VLOOKUP関数関数のFALSE（または０）と同じです。
そのほか１，２，-１から選びます。
| 数値 | 意味 |
|---|---|
| ０ | 完全一致。一言一句合っている値から検索する |
| -1 | 一致するものがなければ、次に小さい値を検索する |
| 1 | 一致するものがなければ、次に大きい値を検索する |
| 2 | 検索値にワイルドカードを使う場合、これにする |
それぞれ詳しく見ていこう。
※練習データを使っている方は、Sheet2を開いてください。
一致モード、-1と1の違い
次のデータは検索値75ですが、検索範囲に75はありません。
一致モードを「-1」にすると、75の次に小さい60が該当し、「B」が取り出せました。
一致モードを「1」にすると、75の次に大きい80が該当し、「A」が取り出せました。
一致モード、2
検索値に「*」や「?」を使ってあいまい検索する場合は、一致モードを2にします。
ワイルドカードってやつだね！
XLOOKUP関数の検索モードとは
検索モードは、どの順に検索していくかです。
VLOOKUP関数では上から順に見ていくので、検査値と同じ値がいくつかあっても、一番上だけを見ます。
それだとちょっと都合が悪い！という時に、下から順に見ることもできるんです。
| 数値 | 意味 |
|---|---|
| 1 | 先頭から順に検索する（省略すると、これになる） |
| -1 | 末尾から順に検索する |
| 2 | バイナリ検索で先頭から検索（昇順に並べ替えした場合有効） |
| -2 | バイナリ検索で末尾から検索（降順に並べ替えした場合有効） |
検索モード、1にした場合
1または検索モードを省略すると、先頭から末尾に向かって検索していきます。
検索範囲に営業が2つありますが、検索モードを「1」にすると、上にある「はらの」が取り出せました。
検索モード、-1にした場合
検索モードを「-1」にすると、下にある「つきの」が取り出せました。
検索モード、2,-2にした場合
2,-2のバイナリ検索って、何なの？
めっちゃ簡単に言うと、高速で検索できるよってこと。
普通は1か-1でいいと思いますが、データが何千何万とある場合は、こちらを選ぶと素早く検索してくれますよ！
ただし、2は昇順、-2は降順に並び替えしておかないといけないので、忘れないようにしてくださいね。
もしExcel365をお使いなら、VLOOKUP関数よりXLOOKUP関数を使ってみてはいかがでしょうか？
他にもExcel365でしか使えない関数も、色々ありますよ！
Excel365って、どうやったら使えるの？
それにはOffice365、つまりサブスク版のOfficeが必要です。
詳しくはこちらのページに、まとめました！
それでは、お疲れさまでした！
- 検索する関数は、VLOOKUP関数より簡単で便利なXLOOKUP関数！
- =XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)
- 見つからない場合以降は、省略可能
- ただし、対応バージョンのExcel365、Excel2021じゃないと使えない