• Excel
  • 関数
  • MicrosoftOffice365

XLOOKUP関数とは?VLOOKUP関数との違いや使いこなすためのポイントを解説!

2022.01.27

XLOOKUP関数とは?VLOOKUP関数との違いや使いこなすためのポイントを解説!

目次

Excel(エクセル)を使った業務をしていると、一度はExcelファイルの中の値を探したり、取り出す業務をしたことがあるのではないでしょうか?

そのような業務の中で多く使われる関数にはVLOOKUP関数やHLOOKUP関数がありますが、さらに便利に使える、一つの関数で縦横方向のデータが検索可能になった「XLOOKUP関数」が2020年1月にリリースされました。

今回は、VLOOKUP関数とHLOOKUP関数の後継として、圧倒的に使いやすく便利になった「XLOOKUP関数」の使い方のポイントをご紹介します。


EXJ_みずき

監修:みずき さん
EXCEL女子の研修講師をしています。Excel研修では「覚える」だけではなく「理解する」ことで、自身の力としてExcelスキルを身につけられる研修を実施しています。研修をするたびにExcelが大好きになり、Excelってすごい!と実感しています。Excelの他には写真が好きで食べ物や風景を撮影しています!

XLOOKUP関数とは

まず、XLOOKUP関数の数式は下記の通りです。

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

範囲または列ごとに情報を検索し、該当するデータを取り出す関数です。
  • 検索値:どのデータで
  • 検索範囲:どこを検索して(配列または範囲)
  • 戻り範囲:どの範囲の値を取り出すか
  • 見つからない場合:検索値が見つからない場合何を表示するか(省略した場合:「#N/A」)
  • 一致モード:完全一致か、近似値も検索するか(初期値:完全一致)表1
  • 検索モード:どのような順序で検索するか(初期値:先頭から末尾)表2

6つの引数のうち、検索値、検索範囲、戻り範囲の3つは必須、[見つからない場合]、[一致モード]、[検索モード]の3つは省略可能な引数です。

一致モード(表1)

設定値 説明
0または省略 完全一致。見つからない場合は「#N/A」が表示されます。(既定の設定)
-1 完全一致。見つからない場合は次に小さい項目が表示されます。
1 完全一致。見つからない場合は次に大きい項目が表示されます。
2 *、?、~(チルダ)などのワイルドカードとの一致。

検索モード(表2)

設定値 説明
1または省略 先頭から末尾に向かって検索します。(既定の設定)
-1 末尾から先頭へ逆方向に検索します。
2 昇順に入れ替えられた検索範囲を使ってバイナリ検索します。(※)
-2 降順に入れ替えられた検索範囲を使ってバイナリ検索します。(※)

(※)バイナリ検索とは、真ん中から半分に分けて条件に合わない方を消す、という作業を複数回繰り返すことで候補を絞り込んでいく検索方法です。計算量のデータが膨大なときに使用します。

それでは次に、VLOOKUP関数と比較しながら、XLOOKUP関数のおさえておきたいポイントを確認していきましょう。

XLOOKUP関数とVLOOKUP関数の違いは?

XLOOKUP関数にはVLOOKUP関数にはない様々なポイントがあります。VLOOKUP関数では実現するのに手間がかかっていた事やできなかった事も、XLOOKUPでは簡単に実現できるようになりました。

以下5つのVLOOKUP関数との違いや使い方のポイントを押さえ、XLOOKUP関数を業務に活用してみてください。

範囲選択が自由に指定可能

VLOOKUP関数は縦方向のみを検索します。またVLOOKUP関数は検索範囲が左端にないと検索ができません。XLOOKUP関数は縦横指定なく、検索範囲を自由に指定することができます。そして、検索範囲が左端にない場合でも検索範囲を指定することが可能です。
XLOOKUP関数の範囲選択

検索値が見つからなかった場合に表示するデータを指定可能

VLOOKUP関数では検索値が見つからなかった場合、#N/Aエラーを返します。エラー表示をしたくない場合IF関数やIFERROR関数などを組み合わせて使用する必要があります。一方、XLOOKUP関数の場合は、引数の[見つからない場合]に表示する値を指定することができるため、一つの関数で完結させることができます。
XLOOKUP関数の見つからない場合

「一致モード」は完全一致が既定設定

VLOOKUP関数は、完全一致で検索したい場合、引数の「検索の型」にFALSEを指定する必要があります。省略するとTRUE型となり近似値一致での検索となります。一方、XLOOKUP関数は完全一致が既定の設定となっており、引数の「一致モード」を省略しても完全一致で検索することができます。

「&」を使って複数条件で検索可能

例えば以下の表のようにいちごの価格を検索する場合、一致するデータが複数あるとVLOOKUP関数では検索できません。そんな時は「&」を使って商品番号と商品名のふたつの列を連結させた値で検索し、検索範囲も連結させて検索します。複数列にわたる条件で検索することができるところもXLOOKUP関数の大きな特徴です。
スピル機能

戻り範囲は元データの列番号が変わっても修正不要

データを取り出す際、VLOOKUP関数は左から数えた「列番号」を指定しています。そのため、元データの列数が増減してしまうと列番号(指定列)が変わり、元データに変更があった場合は、その都度列番号を指定し直す必要があります。一方、XLOOKUP関数はセル範囲で指定するため、元データに変更があっても関数を修正する必要はありません。
image_excel-xlookup_014

以上、5つのポイントについてご紹介しました。このポイントを踏まえたうえで、さっそくXLOOKUP関数を使用してみましょう。

↑ 記事TOP

XLOOKUP関数の使い方

まず、XLOOKUP関数を使用する前に、ご自身が使用しているExcelがXLOOKUP関数を使用できるバージョンかどうか、確認しましょう。

XLOOKUP関数は、Microsoft365またはExcel2021でのみ使用することができます。永続ライセンス版のExcel2016やExcel2019では使用することができませんが、永続ライセンス版のExcel2021では使用可能です(2023年4月現在)また、使用できるはずのMicrosoft365やExcel2021で、数式バーに「=X」と入力してもXLOOKUP関数が表示されない場合は、プログラムを更新する必要があります。

(1)Excel《ファイル》から《アカウント》をクリックする

(2)《更新オプション》から《今すぐ更新》をクリックする

更新オプション
更新が完了したら、XLOOKUP関数が使用できるようになります!
※企業によってはアップデートが配信されていない場合もあります。

使用例1.商品No.から商品名を検索する

こちらは商品の注文表です。商品No.を入力して、該当する商品名を商品リストから検索するXLOOKUP関数を作成します。今回はセル【C3】に入力された商品No.「P0008」の商品名を検索し、セル【C4】に入力させます。

商品Noから商品名を検索する01

(1)セル【C3】に「P0008」と入力する

(2)セル【C4】の数式バーに「=XLOOKUP(C3,E4:E13,G4:G13)」と入力する
(この数式は、セル【C3】に入力された値をE列から探し、その商品No.の商品名を探して表示してください。という意味です。)
XLOOKUP関数では、[検索範囲]と[戻り範囲]で指定するセル範囲は、同じ高さに指定しないと数式の結果がエラーとなります。
商品Noから商品名を検索する02

VLOOKUP関数のように左から何列目かなど数えることなく、簡単に検索結果を表示することができます。
商品Noから商品名を検索する03

該当しない商品No.に「該当なし」と表示させる

先ほどは必須の引数のみを使用して作成しましたが、省略可能な引数も使用して検索してみましょう。こちらのシートには「P0010」までの商品No.しか存在しませんが、存在しない商品No.「P0011」を入力した時、セル【C4】に「該当なし」と表示させるよう指定してみましょう。

(1)セル【C3】に「P0011」を入力しておく

(2)セル【C4】の数式バーに「=XLOOKUP(C3,E4:E13,G4:G13,"該当なし")」と入力する

商品Noから商品名を検索する04

商品No.「P0011」はこの表には存在しないので、「該当なし」と表示されました。
商品Noから商品名を検索する05
IFERROR関数とVLOOKUP関数を組み合わせた数式にしなくても、XLOOKUP関数一つで検索値が見つからなかった場合の表示方法も指定することができます。

使用例2.商品名から商品No.を検索する

次は、先ほどと同じ表を使用して商品名を入力し、商品No.が表示される数式を省略可能の引数も取り入れて作成してみましょう。ここでは複数存在する商品名「プリンター」の中で一番下にある「P0010」がセル【C4】に表示されるようにすることとします。

(1)セル【C3】に「プリンター」と入力しておく

(2)セル【C4】の数式バーに「=XLOOKUP(C3,G4:G13,E4:E13,"該当なし",0,-2)」と入力する
(この数式は、セル【C3】に入力された値をG列から探し、その商品名の商品No.を探して表示してください。ただし、下から数えて最初に一致するものとします。という意味です。)
商品名から商品Noを検索する01
セル【C4】には「プリンター」の中で1番下に入力されている「No.0010」が表示されました。

商品名から商品Noを検索する02

今回は、省略可能な引数も全て使用して作成しました。省略可能の3つの引数のどれか一つでも使用する場合はXLOOKUP関数の構文に沿って、すべて順に入力する必要がありますのでお気をつけください。

まとめ

今回は、Microsoft365とExcel2021(2023年4月現在)で使用可能な「XLOOKUP関数」についてご紹介しました。

XLOOKUP関数は、VLOOKUP関数の複雑な部分が改善されたとてもシンプルでわかりやすい関数です。XLOOKUP関数を上手に使いこなして、業務効率をグン!とあげていきましょう。

当社では、『Microsoft認定トレーナー』資格を持つEXCEL女子による「Excel研修」を提供しております。書籍化するなど注目いただいている研修です。

社員教育に時間を割けない、Excelスキルの底上げをしたいなどお悩みでしたら、ぜひお気軽にお問い合わせください。

Excel研修のサービス案内資料

Excel研修のサービス案内資料

実際に現場で活躍するEXCEL女子が考えたカリキュラムをもとに、学習するExcel研修のご紹介です。
資料ダウンロード
PAGE TOP