「XLOOKUP関数ってどうやって使うの？」
「XLOOKUP関数とVLOOKUP関数との違いが知りたい」
こんな疑問を解消します。
エクセルの中で最も使用頻度の高い関数の1つがVLOOKUP関数ですが、「検索元のデータが検索値の左にないといけない」といったようにいくつか不便な点がありました。
そこで登場したのがXLOOKUP関数です。
XLOOKUP関数は比較的新しい関数ですが、VLOOKUP関数の上位互換といっても過言ではない便利な関数になっています。
本記事ではXLOOKUP関数の使い方やXLOOKUP関数とVLOOKUP関数の違いを実例付きでわかりやすく解説します。
XLOOKUP関数ってなにができるの？
XLOOKUP関数は特定の値をもとにして、別の表データ(データベース)から値を取得することができる関数です。
なので基本的にできることはVLOOKUP関数と似ています。
「それならVLOOKUP関数でいいのでは？」と思うかもしれませんが、VLOOKUP関数で不便であった点が解消されており、さらにVLOOKUP関数ではできなかったことも可能になったりと、より便利になった関数がXLOOKUP関数です。
XLOOKUP関数とVLOOKUP関数の違いはなに？
XLOOKUP関数とVLOOKUP関数の違いを説明します。
上記のようなデータがあったとします。
ここから「営業IDを元にして営業名を取得したい」という要望があった場合、今までのやり方であればVLOOKUP関数を使用していました。
ただしこのVLOOKUP関数によるデータの取得には一つ弱点があります。
それは、
取得元となるデータが、検索したい値より左側になければいけない
というものです。
上記のような表であれば「営業ID」が「氏名」の左にあるのでVLOOKUP関数でも問題なく取得できます。
しかし以下のような表があった場合はどうでしょうか？
この表では取得したい「氏名」よりも「営業ID」が右にあります。
この場合VLOOKUP関数を使うことは不可能です。
これをVLOOKUP関数でやりたい場合には、取得する際のキーとなる「営業ID」を「氏名」の左に移動させてあげる必要がありました。
ただこの作業は面倒ですよね。
この手間を解消してくれるのがXLOOKUP関数になります。
XLOOKUP関数では、検索のキーとなるデータがどこにあっても検索&取得が可能です。
基本的にXLOOKUP関数ではVLOOKUP関数でできることはほぼ可能であり、VLOOKUP関数の不便な点を克服しているため上位互換のような関数となっています。
ではここからはXLOOKUP関数の使い方をみていきます。
XLOOKUP関数の使い方
今回は以下の表を例にして、XLOOKUP関数を使ったデータの取得をみていきます。
まずXLOOKUP関数の構文を確認してみましょう。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
XLOOKUP関数の引数は全部で6つです。
最初の3つの引数が必須で、4つめ以降の引数は任意となります。
それぞれの意味をみていきます。
XLOOKUP第1引数：検査値
XLOOKUP関数の第1引数は「検索値」です。
例えばBの列に右の営業表から取得した「氏名」を表示させたいとします。
この場合は左の表にある「営業ID」をキーにすることで、右の営業表から「氏名」を取得することが可能です。
このデータを探しにいく際のキーとなる左の表の「営業ID」を第1引数に指定します。
今回は左の「営業ID」をもとに右の表の「氏名」を探すのでA列のセルを第1引数に指定します。
VLOOKUP関数ではA3セルだけを選択してそれをオートフィルでコピーしていたかと思いますが、XLOOKUP関数はスピルが使用されているので引数に配列を指定することが可能です。
ドラッグしてA3:A5のように範囲選択になるようにします。
スピルについてよくわからない方は以下の記事を参考にしてみてください。
XLOOKUP関数第2引数：検索範囲
XLOOKUP関数の第2引数は検索範囲です。
ここには取得先となる表の中で、第1引数に選んだキーが含まれる範囲の指定をします。
今回は「営業ID」を検索のキーとして指定しているので、F3:F5を選択します。
XLOOKUP関数第3引数：戻り範囲
XLOOKUP関数の第3引数は戻り範囲です。
戻り範囲というとわかりにくいですが、簡単にいえば取得したいデータの範囲になります。
今回は営業表の「氏名」を取得したいのでE3:E5を指定します。
これで入力が必須となっている3つの引数を指定できました。
ここでエンターを押して確定すると、「営業ID」を検索キーとして営業表の「氏名」を取得することができていることが確認可能です。
XLOOKUP関数第4引数：見つからない場合
XLOOKUP関数の第4引数は関数の結果値が見つからない場合の値になります。
例えば検索キーの営業ID1が営業表になかった場合は以下のように#N/Aエラーが表示されます。
VLOOUP関数でもこのようなエラーが出ることはありましたが、その場合にはiferror関数などを組み合わせることでこの#N/Aを回避していました。
XLOOKUP関数ではこのような作業をしなくても第4引数に見つからなかった場合の指定をすることでエラーの代わりの値を表示させることができるようになります。
例えば、検索キーで探したけれど値が見つからなかった場合に「該当なし」と表示させたい場合には以下のように第4引数に指定することで可能です。
XLOOKUP第5引数：一致モード
XLOOKUP引数の第5引数は一致モードです。
ここには０、または1を入力します(何も入力しない場合は0になります)。
0を入力した場合は検査値と完全に一致するもののみを返す、1を入力した場合は一致していなくても一番近いものを返すという方法です。
1にすると間違いのもとになるので、基本的には0を指定すればよいかと思います。
XLOOKUP関数第6引数：検索モード
XLOOKUP関数の第5引数は検索モードです。
ここには1または-1を入力します。
これはデータを探す時に昇順(1で指定した場合)で探すか、降順(-1で指定した場合)で探すかを指定可能です。
例えば探そうとしているデータが重複している場合に、1を指定していれば上にあるデータ、-1を指定していれば下にあるデータが取得されます。
第5引数に1を指定した場合(上のデータが取得される)
第5引数に-1を指定した場合(下のデータが取得される)
XLOOKUP関数の使い方まとめ
今回はXLOOKUPの使い方を紹介しました。
とても便利な関数なのでVLOOKUP関数をよく使う人はぜひ使ってみてください。
今回の記事が参考になれば幸いです。