XLOOKUP関数とは

XLOOKUP関数は、特定のデータを指定した範囲から探し出し、それに対応した値を取り出すことができる関数です。

たとえば以下の例のように、G4のセルに出席番号を入力するだけでH4、I4、J4に、その出席番号の生徒の名前や生年月日が自動で出力されるようにすることなどができます。

具体的な使い方の前に、XLOOKUP関数の役割や特徴を確認しきましょう。

VLOOKUP関数とは

「XLOOKUP関数」と名前の似ている関数に「VLOOKUP関数」がありますが、XLOOKUP関数は”VLOOKUP関数の進化バージョン”として2020年に登場した関数です。

VLOOKUP関数は、実務でも活用される場面の多い重要な関数の1つです。

VLOOKUP関数では、指定した表を参照し、そのなかから一致したデータを転記することができます。

XLOOKUPと同じく、出席番号をもとに氏名データを抽出したり、アンケート回答者の回答者番号をもとに年齢を抽出したりといったように、様々な活用方法があげられます。

VLOOKUPの関数の書き方は、以下の通りです。

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

VLOOKUP関数については、以下の記事で詳しく紹介しているので、興味のある方は是非読んでみてください。

VLOOKUP関数とは　特徴や使い方をExcel初心者にも分かりやすく解説

XLOOKUP関数とVLOOKUP関数との違い

VLOOKUP関数では、「範囲」と「列番号」を指定する方法でデータを取ってきます。

そのため、もし列番号が何十行～何百行もあるデータのなかから探してくるとなった場合、列番号を確認するのも一苦労です。また、列番号を間違えてしまうリスクもあります。

XLOOKUP関数は列番号を数える必要がない

一方、XLOOKUP関数では「検索範囲」と「返す範囲」を指定するので、わざわざ列番号を数える必要がありませんので、かなり使いやすくなりました。

また、VLOOKUP関数では検索値は必ず表の左端でしたが、XLOOKUP関数では左端じゃなくても問題ないので、より汎用性が高く、使いやすくなったといえます。

XLOOKUP関数の基本式

さて、ここからはXLOOKUP関数の使い方について見ていきましょう。

XLOOKUPでは、以下の基本式にのっとり値を入力していきます。

=XLOOKUP（検索値,検索範囲,返す範囲,見つからない場合,一致モード,検索モード）

それぞれ以下のような意味を持っています。

• ［検索値］（必須）・・・どのデータで
• ［検索範囲］（必須）・・・どこを検索して
• ［返す範囲］（必須）・・・どの範囲にある値を取り出すか
• ［見つからない場合］（省略可）・・・検索値が見つからなかった場合に、表示するメッセージを指定することができます。
• ［一致モード］（省略可）・・・一致モードは、「検索値」との一致の判定基準のことです。「0」or省略で完全一致となります。
• ［検索モード］（省略可）・・・検索する順序を指定します。「1」or省略で、先頭→末尾の順で検索します。

「見つからない場合」「一致モード」「検索モード」は省略可

一見、記載しないといけない内容が多い関数のように思えるかもしれません。

しかし、「見つからない場合」「一致モード」「検索モード」は省略可能な部分です。

実際、「一致モード」は、基本的に完全一致で検索することが多いと思うので、省略することが多いです。

また、「検索モード」も、先頭→末尾の順で検索するのが一般的なので、こちらも省略することが多いでしょう。

「見つからない場合」も、見つからなかったときに何も表示する必要がなければ不要です。

そのため必須の部分は、「検索値」「検索範囲」「返す範囲」だけなので、非常にシンプルになります。

XLOOKUP関数の基本的な活用イメージ

式だけを見てもイメージがつきにくいかと思いますので、ここからは基本的な使い方を具体例でご紹介していきましょう。

まずは、以下のようなデータがあったとしましょう。

こちらはとある高校のクラスに属する20名の架空データです。

出席番号からその他の情報を抽出する

こちらのデータの中から「出席番号」をもとに、その他の情報を引っ張ってきたいと思います。

まず、以下のようにデータを返すためのセルを用意します。

続いて、「名前」「生年月日」「血液型」にXLOOKUP関数を使った式を入力していきます。

まず、「名前」から見ていきます。

上述の通り、XLOOKUP関数の式は以下の通りなので、こちらに則って入力していきます。

=XLOOKUP（検索値,検索範囲,返す範囲,見つからない場合,一致モード,検索モード）

まず、検索値は出席番号なので、「G4」を入力します。

続いて、検索範囲は出席番号が並んでいる範囲を探せば良いので、「B2:B22」とします。

最後に返す値は、名前が並んでいる範囲を探せば良いので、「C2:C22」とします。

まとめると、以下のような式になります。

「=XLOOKUP(G4,B2:B22,C2:C22)」

これで完了です。

試しに出席番号に「3」と入れてみましょう。

そうすると、ちゃんと出席番号が「3」の「大橋 彩」さんの名前を返すことが出来ました。

同じように、「生年月日」「血液型」も入力していきます。

すると、以下のように正しく出席番号3番の方の「生年月日」「血液型」データを返すことが出来ました。

試しに、出席番号の部分を5に変えてみると、以下のようになります。

12にしてみると、以下の通り。

このように、出席番号の部分だけ変えるだけで、対応した正しいデータを返すことが出来るようになりました。

今回の例では20名なので目視でもなんとか出来そうですが、大量のデータがあるなかで複数の項目について確認する必要がある場合などでは、かなり大変です。

そんなときXLOOKUP関数が使えると、簡単に欲しいデータを抽出することが出来るので大変便利です。

XLOOKUP関数の注意点

XLOOKUP関数の注意点として、「検索範囲」と「返す範囲」の高さをそろえる必要があります。

｢検索範囲｣ と ｢返す範囲｣ の高さをそろえる

たとえば先ほどの例で、出席番号に対応した名前を返すというXLOOKUP関数の式をつくるとします。

その際、以下の画像のように「検索範囲」は“B2:B22”なのに、「返す範囲」は“C3:C22”というふうに高さが異なる状態になってしまうとXLOOKUP関数が使えません。

この状態でEnterを押すと、以下のようにエラーが返ってきます。

XLOOKUP関数を使うときは、「検索範囲」と「返す範囲」の高さをそろえるということを忘れないようにしてください。

ビジネスにおける活用イメージ

ここからは、XLOOKUP関数のビジネスにおける活用イメージをご紹介します。

ケース1　請求書での活用

XLOOKUPは、請求書を作成する場面でも便利な関数です。

たとえば、以下のような架空の請求書があったとします。

このとき、「商品コード」をもとに、「品名」「単価」を返すようにXLOOKUP関数で設定します。

そうすると、「商品コード」を入力するだけで、自動で「品名」と「単価」が出てきてくれます。

一つ入力すれば、そこから下の行はオートフィルを使うことで、まとめて品名や単価を入力することができます。

（※オートフィルを使う場合は、検索範囲や返す範囲がずれないように絶対参照を使うなどの工夫も必要ですのでご注意下さい）

XLOOKUP関数を使うことで、請求書発行業務を効率化できるだけでなく、転記ミスの防止にもつながるという点で有効な活用方法の一つです。

ケース2　売上実績をまとめる

例えば、カフェにおける各商品の売上を以下のようにExcelシートにまとめる業務があるとしましょう。

この例でもXLOOKUP関数を使うことで、「商品コード」だけ入力すれば、それに対応した「品名」や「単価」が自動で出てきてくれます。

このように細かいデータを集計する際、手動でおこなうと効率が悪く、ミスしてしまう可能性も高いので、XLOOKUP関数で自動化することで効率化できます。

まとめ

今回はVLOOKUPの進化版であるXLOOKUP関数について、その特徴をビジネスにおける活用例とともにご紹介しました。

基本的な用途としてはVLOOKUP関数と同じです。

VLOOKUP関数も大変便利な関数で使える場面も多いですが、扱うデータによってはXLOOKUP関数の方が便利なこともあります。

XLOOKUP関数は、Excelを使ったデータの集計や分析をよく行うという方であれば知っておいて損はない関数ですので、是非この機会に覚えておきましょう。

＼ この記事を読んでいる人におすすめ！ ／