【Excel】XLOOKUP関数とは何か？　VLOOKUP関数とは何が違う？

2020年1月に登場したExcelのXLOOKUP関数、皆さんは使っていますか？存在は知っているけど、“VLOOKUP関数との違いがよくわからない”という声をよく耳にするので、今回はXLOOKUP関数を説明したうえで、VLOOKUP関数との違い、特にXLOOKUPの利便性について解説していきます。

XLOOKUP関数とは何か
XLOOKUP関数の使い方
1. 構文
2. 使用例
VLOOKUP関数よりもXLOOKUP関数を使うべき理由

XLOOKUP関数とは何か

XLOOKUP関数は、あるデータテーブルのセルに、別のテーブルのセルを参照したい時に使用する関数です。ビジネスの三大関数の一つであるVLOOKUP関数の機能を内包する関数ですので、VLOOKUP関数を使用している人にとっては理解しやすい機能かと思います。もしVLOOKUP関数をよく知らない人は、以下の記事をご覧ください。

XLOOKUP関数の使い方

構文

XLOOKUP関数の使い方は以下の通りです。

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

ただし、必須の引数は前半の3つであり、日常使用するのも必須引数が多いです。必須引数のみでXLOOKUP関数を使ったがうまくいかないというケースでも、必須引数以外が必要なことよりも、必須引数が間違っていることが多いです。

そこで、以下では必須の引数3つに絞って説明します。必須引数以外については今後別記事で説明します。

以下が、XLOOKUP関数の必須引数3つと、比較できるようにVLOOKUP関数の引数を示しています。

=XLOOKUP((検索値, 検索範囲,戻り範囲)
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])

使用例

では具体的に、XLOOKUP関数の使い方をみていきましょう。

今回は以下の表を使います。この表のG列の、販売価格(単価)が空欄になっているので、価格情報が記載されている他のテーブルを参照するとします。

“価格マスター”シートには、以下の通りバナナとイチゴの単価が記載されています。これを、先ほどの表の”販売価格(単価)”に引用しましょう。

G3セルに以下のようにXLOOKUP関数を用いることで、別シートのバナナの価格を引用できました。

引数について説明します。

まず検索値ですが、”バナナ“という単語で検索したいので、B3セルの”バナナ“を参照しています。

次に検索範囲です。”バナナ”という検索値をどこから見つけたいか、という引数です。今回は“価格マスター”シートのB列に”バナナ”を含めた商品名が記載されているので、“価格マスター”シートのB列(B:B)を指定します。

最後に戻り範囲です。戻り範囲という概念が理解できなくて躓く人も多いと思いますので、直感的な説明を心がけます。検索値と検索範囲によって、B3セルに記載されている”バナナ“という商品名を、”価格マスター”シートのB列から探してくる、という条件まで指定できています。検索結果として、価格マスターシートB列の”3行目”に”バナナ“が見つかります。あと必要なのは、“バナナ”の”単価”を探してほしい、という条件です。そこで、検索結果の”行”の、どの”列”の値を関数の結果として表示してほしいか、を指定します。これが戻り範囲です。今回でいうと、“C列”の値を関数の結果として表示してほしいです。そこで、戻り範囲は“価格マスター”シートの”C列”を指定します。

VLOOKUP関数よりもXLOOKUP関数を使うべき理由

上記の説明を読むと、結局XLOOKUP関数とVLOOKUP関数は、できることが同じであるように思われるかもしれません。しかし実際には、XLOOKUP関数はVLOOKUP関数の上位互換であり、XLOOKUP関数にはあって、VLOOKUP関数にはない利便性が存在します。以下ではその利便性を紹介します。

検索範囲・戻り範囲の指定が直感的で簡単

VLOOKUP関数のデメリットの一つに、”列数を数えるのが面倒”という点がありました。例えば上の例で、価格マスターシートが以下のように複雑だった場合を想定してみましょう。

先ほどの、XLOOKUP関数を投入したセルに、上の価格マスターを基にVLOOKUP関数を投入すると、以下のような式になります。

“範囲”として”価格マスター!C2:K5″と表全体を指定しないといけない手間もさることながら、列数を”9″と指定しないといけない部分が非常に面倒です。左側から目視で”1,2,3,4,…”と数える必要があります。参照先のテーブルが大きいほど、この列数は数字が大きくなります。20列目とか30列目を指定しようとすると、間違いも頻発します。

こういったケースでも、XLOOKUP関数であれば、検索範囲と戻り範囲をそれぞれ1つの列で指定できるので、作業が非常に楽です。具体的には以下のような数式になります。

検索範囲も戻り範囲も、列全体を選択するだけでよかったので、体感ではVLOOKUP関数入力の1/3くらいの時間で入力できました。

戻り範囲が検索範囲より左にあっても良い

VLOOKUP関数のもう1つのデメリットに、”検索値の存在する列より左の列は指定できない”というものがありました。例えば今回のケースで、以下のように価格ではなく商品のIDを引用したい時、XLOOKUP関数であれば以下のように簡単に引用できます。

しかし、これはVLOOKUP関数では達成できません。なぜなら引用元の価格マスターシートで、”ID”が、検索値である”商品名”よりも左の列だからです。これがXLOOKUP関数を使うべき2つ目の理由です。

HLOOKUPの機能も備えている

XLOOKUP = VLOOKUP + HLOOKUP

XLOOKUP関数は、VLOOKUP関数だけでなく、HLOOKUP関数の機能も備えています。HLOOKUP関数というのは、VLOOKUP関数の”タテヨコ逆バージョン”です。VLOOKUPのVはVerticalのV、つまり縦(列)方向に値を検索してくれますが、それに対してHLOOKUP関数のHはHorizontalで、水平(行)方向に値を検索してくれます。XLOOKUPのXは、”タテもヨコも(行も列も)”という意味であるとされていることからも、XLOOKUP関数がVLOOKUPとHLOOKUPの両方を兼ねるものとして考案されたことが窺えます。