【Excel・エクセル】XLOOKUP関数とは?使い方やVLOOKUP関数との違いなど

抽出,検索

XLOOKUP関数の使い方
もりの

XLOOKUP関数とは、なんぞ?VLOOKUPと違うの?

やまの

使えるなら使った方がいいよ、使い方を教えるね。

今回のポイント
  • 検索する関数は、VLOOKUP関数より簡単で便利なXLOOKUP関数!
  • =XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)
  • 見つからない場合以降は、省略可能
  • ただし、対応バージョンのMicrosoft365、Excel2021じゃないと使えない

スポンサーリンク

【Excel・エクセル】XLOOKUP関数とは?

XLOOKUPの使用例
XLOOKUPの使用例

検索の関数はVLOOKUP関数が有名ですが、検索値が必ず左にないといけなかったり、列番号を数える手間があったりと、面倒な部分もありますよね。

そこで使ってほしいのが、XLOOKUP関数!

VLOOKUP関数より引数の指定が簡単で、範囲も柔軟に指定できる、スーパー便利検索関数なんです。

さらにスピル可で、隣接する情報も一緒に抽出できるので効率がよく時短につながります。

やまの

スピルは範囲外の値も自動的に検索対象に含む機能。後で説明するね。

XLOOKUPの対応バージョン

もりの

え、じゃあもうVLOOKUP関数使わなくてもいいのでは?

XLOOKUP関数は新しい関数で、Microsoft365かExcel2021でないと使えません

そう、Excel2019や2016では、XLOOKUP関数は使えないんです……

やまのの笑った顔
やまの

その時はVLOOKUP関数か、MATCH関数とINDEX関数を組み合わせて検索してね。

ちなみに読み方は、「エックスルックアップ関数」です。

VLOOKUP関数に比べ、柔軟性と効率性を兼ね備えたXLOOKUP関数。

さっそく、使い方を見てみましょう。

動画でもご紹介しています!

【Excel・エクセル】XLOOKUP関数の使い方(基本編)

XLOOKUP関数の書き方

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])

検索値この値を
検索範囲この範囲から探して
戻り範囲その位置にある、この範囲の値を教えて
見つからない場合もしなかったら、こうして(省略可)
一致モード完全一致か、少し違ってもいいか (省略可)
検索モード検索値をどうやって検索するか (省略可)
もりの

え、待って、引数多くて難しそうじゃない?

やまの

大丈夫、基本は戻り範囲までしか使わないから。

セルF2の従業員NoからXLOOKUP関数を使って、入社日を抽出したいと思います。

XLOOKUPの練習問題
見せてもらおうか、XLOOKUPの実力とやらを!

※実際に操作できます。

いちいちスクロールするのは面倒なので、別タブか別ウィンドウで開くの推奨です!

やまの

=XLOOKUP(F2,A2:A7,D2:D7)で、求められるよ。説明するね。

ステップ1

セルG2に「=XLOOKUP(」と入力してください。

「=XLOOKUP(」まで入力した画像
まずは関数名

ステップ2

次に検索値です。

今回はセルF2の従業員のNoをもとに検索するので、セルF2をクリックします。

そして「,(カンマ)」

XLOOKUP関数の検索値を指定した画像
何を基準に検索するか

ステップ3

次に検索範囲。
これは検索値を含む列(または行)ですね。

今回は従業員のNoが書かれたセルA2からA7までをドラックして指定します。

XLOOKUP関数の検索範囲を指定した画像
検索範囲は、検索値を含む範囲
やまの

「,(カンマも入れてね。

ステップ4

最後に戻り範囲、取り出したい値がある範囲を選択します。

今回は入社日の値を取り出したいので、セルD2からD7までをドラッグですね。

XLOOKUP関数の戻り範囲を指定した画像
戻り範囲は、欲しい値が書いてある範囲
やまの

ここで一つ、注意があるよ!

それは、検索範囲と戻り範囲は必ず同じ大きさであること

検索範囲がA2:A7で戻り範囲がD2:D8のように、範囲の大きさが違うとエラーになっちまうんです……

XLOOKUPで#VALUE!エラーになった画像
2つの範囲が違うと、「#VALUE!」エラーになる

完成!

これで式は完成です。

エンターキーを押すと、無事、Noに該当する入社日が検索できました!

XLOOKUP関数で値を抜き出せた画像
XLOOKUP関数で値を抜き出せた!

【Excel・エクセル】XLOOKUP関数とVLOOKUP関数の違い

VLOOKUP関数より簡単に同じようなことができると、お分かりいただけたと思います。

では、VLOOKUP関数じゃなくてXLOOKUP関数を使うメリットは、どこにあるんでしょうか?

VLOOKUP関数との違い1.あのスピルができる!

もりの

あのってどの?映画監督の?

やまの

違ぇよ。

スピルは簡単に言うと、該当のセルのついでに続きのセルの値も一緒に持ってきてくれる機能です。

ステップ1

スピりたいときは、戻り範囲を欲しい範囲まで選択します。

戻り範囲にはB2からC7まで選択した画像
名前と部署名を取り出したいので、戻り範囲にはB2からC7まで選択

完成!

するとこの通り、名前と部署名が一度に取り出せました!

名前と部署名を同時に取り出せた画像
名前と部署名を同時に取り出せた!

いちいちVLOOKUP関数をコピーして列番号を変えて……なんてせずに、一括で複数範囲を抽出できますね!

VLOOKUP関数との違い2.検索値は左になくてもいい

VLOOKUP関数は、検索範囲の左端に検索値がないといけませんでした。

VLOOKUP関数のルール
VLOOKUP関数は、検索値が左

が、検索範囲と戻り範囲をそれぞれ指定できるXLOOKUP関数では、左になくても大丈夫なんです。

検索範囲の左側にある列からも、値を検索できますよ

検索値の左の列から値を取り出した画像
検索値の左の列からも、値を取り出せる

VLOOKUP関数との違い3.列番号、検索方法が不要

VLOOKUP関数では、検索値の列から数えて何番目……と、列番号を数えて入れる必要がありました。

しかしXLOOKUP関数は、欲しい列を指定すればいいので、より直感的にできます。

もりの

これはマジうれしい。

また、VLOOKUP関数を使う時はほとんどが完全一致なのに、いちいちFALSEとか0とか検索方法を指定するのもダルいですよね笑

XLOOKUP関数では、省略で完全一致になるので、その手間も省けます!

VLOOKUP関数との違い4.見つからない場合が指定できる

VLOOKUP関数で検索値が範囲にない場合、エラーになってしまいます。

エラーを回避するには、IF関数やIFERROR関数を使わないといけません。

エラー回避にIFERROR関数を使った画像
エラー回避にはIFERROR関数を使う

XLOOKUP関数では、引数で見つからない場合が指定できます。

IFERROR関数などを使わなくても、いなければ空白、なんてここと簡単にできるんです。

XLOOKUP関数内で、見つからない場合を指定した画像
XLOOKUP関数内で、見つからない場合を指定できる

そのほかXLOOKUP関数では、「一致モード」と「検索モード」が指定できます。

【Excel・エクセル】XLOOKUP関数の検索モード・一致モードとは

もりの

あれ、もう抽出できたけど、一致モードと検索モードって何だったの?

やまの

基本は省略していいんだけど、どういう意味があるか見てみようか。

XLOOKUP関数の一致モードとは

一致モードは、VLOOKUP関数の検索方法のようなものです。

省略、または0にすると「完全一致」、VLOOKUP関数関数のFALSE(または0)と同じです。

そのほか1,2,-1から選びます。

数値意味
完全一致。一言一句合っている値から検索する
-1一致するものがなければ、次に小さい値を検索する
1一致するものがなければ、次に大きい値を検索する
2検索値にワイルドカードを使う場合、これにする
やまの

それぞれ詳しく見ていこう。

※練習データを使っている方は、Sheet2を開いてください。

一致モード、-1と1の違い

次のデータは検索値75ですが、検索範囲に75はありません。

一致モードを「-1」にすると、75の次に小さい60が該当し、「B」が取り出せました。

一致モードを-1にした画像
-1だと、75の次に小さい60で検索

一致モードを「1」にすると、75の次に大きい80が該当し、「A」が取り出せました。

一致モードを1にした画像
1だと、75の次に大きい80で検索

一致モード、2

検索値に「*」や「?」を使ってあいまい検索する場合は、一致モードを2にします。

もりの

ワイルドカードってやつだね!

XLOOKUP関数の検索モードとは

検索モードは、どの順に検索していくかです。

VLOOKUP関数では上から順に見ていくので、検査値と同じ値がいくつかあっても、一番上だけを見ます。

それだとちょっと都合が悪い!という時に、下から順に見ることもできるんです。

数値意味
1先頭から順に検索する(省略すると、これになる)
-1末尾から順に検索する
2バイナリ検索で先頭から検索(昇順に並べ替えした場合有効)
-2バイナリ検索で末尾から検索(降順に並べ替えした場合有効)

検索モード、1にした場合

1または検索モードを省略すると、先頭から末尾に向かって検索していきます。

検索範囲に営業が2つありますが、検索モードを「1」にすると、上にある「はらの」が取り出せました。

検索モードを1にした画像
検索モード1は、上から探す

検索モード、-1にした場合

検索モードを「-1」にすると、下にある「つきの」が取り出せました。

検索モードを-1にした画像
検索モード-1は、下から探す

検索モード、2,-2にした場合

もりの

2,-2のバイナリ検索って、何なの?

やまの

めっちゃ簡単に言うと、高速で検索できるよってこと。

普通は1か-1でいいと思いますが、データが何千何万とある場合は、こちらを選ぶと素早く検索してくれますよ!

ただし、2は昇順、-2は降順に並び替えしておかないといけないので、忘れないようにしてくださいね。

もしExcel365をお使いなら、VLOOKUP関数よりXLOOKUP関数を使ってみてはいかがでしょうか?

他にもExcel365でしか使えない関数も、色々ありますよ!

もりの

Excel365って、どうやったら使えるの?

それにはOffice365、つまりサブスク版のOfficeが必要です。
詳しくはこちらのページに、まとめました!

それでは、お疲れさまでした!

今回のおさらい
  • 検索する関数は、VLOOKUP関数より簡単で便利なXLOOKUP関数!
  • =XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)
  • 見つからない場合以降は、省略可能
  • ただし、対応バージョンのExcel365、Excel2021じゃないと使えない