【Excel・エクセル】XLOOKUP関数とは?使い方からVLOOKUP関数との違いまで分かりやすく

もりの

VLOOKUP関数より便利な、XLOOKUP関数ってのがあるんだって?

やまの

XLOOKUP関数は便利だよ!使い方や、VLOOKUP関数との違いを見てみよう。

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

スポンサーリンク

 XLOOKUP関数の練習ファイルダウンロード

もりの
 
 

一緒にやってみませんか?

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

XLOOKUPの使用例
XLOOKUPの使用例

検索の関数と言えばVLOOKUP関数が有名ですが、検索値が左にないといけなかったり、列番号を数えたりと、ちょっと面倒な部分もありますよね。

今回はそんな不満が解消された、XLOOKUP関数についてご紹介します!
控えめに言って、結構簡単にできて、さらにVLOOKUP関数より便利になっています。

もりの

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

やまの

ただ、XLOOKUP関数はExcel365か、Excel2021じゃないと使えないんよ。

そう、なのでExcel2019や2016では、XLOOKUP関数は使えません……

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

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

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

XLOOKUP関数の書き方

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

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

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

やまの

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

XLOOKUP関数の使用例

セルF2の値をもとに、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のように、範囲の大きさが違うとエラーになっちまうんです……

完成!

これで式は完成です。

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

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

これまでで、ほぼ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関数の一致モードとは

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

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

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

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

一致モード、-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じゃないと使えない