本日は、Excel2021からの新機能でもあるXLOOKUP関数について触れていきたいと思います。
XLOOKUPは「eXtended Lookup」（拡張検索）の略で、従来のVLOOKUPやHLOOKUP関数の制限を超えてより自由度の高い検索を行うことができる関数です。
このXLOOKUP関数を完全攻略していきたいと思います。これであなたもXLOOKUPマスター！
では、いってみよー！
XLOOKUP関数は、一般的にはExcel2021とMicrosoft365でしか使用できません。
（2024年12月現在）
XLOOKUP関数を完全マスターするための必須知識について
XLOOKUP関数とは？
XLOOKUP関数とは、Excel2021以降に追加された新機能で、以下の関数をまとめたようなことがこの関数ひとつで行えるようになる、いわば次世代の関数です。
- VLOOKUP関数
- HLOOKUP関数
- IFERROR関数
- INDEX関数
- MATCH関数
XLOOKUP関数の公式
XLOOKUP関数の公式は、以下のようになります。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)
必須の入力項目
- 検索値
- 検索したい値をいれます(「コード表のコード」を調べたいなら「コード」が検索値)
- 検索範囲
- 検索値が含まれる別のリストの行または列を選択します(「コード表のコード」が書かれた行または列)
- 戻り範囲
- 表示させたい項目の行または列を選択します(「品名」が書かれた行または列)
任意の入力項目
- 見つからない場合
- エラーが出たときに表示させる文字を指定できます。
- 一致モード
- 完全一致か、近似一致かを指定できます(空白なら「完全一致」)
- 「0」＝完全一致。省略した場合は「0」を入力したことになります。
- 「-1」＝完全一致。見つからない場合は、次に見つかった小さな値になります。
- 「1」＝完全一致。見つからない場合は、次に見つかった大きな値になります。
- 「2」＝「*」 (アスタリスク)、「?」(クエスチョン) および「~」(チルダ) をワイルド カードとして使用できる一致方式です。複雑な条件で対象を探すことができます。
- 完全一致か、近似一致かを指定できます(空白なら「完全一致」)
- 検索モード
- 上から検索するか、下から検索するか選べます(空白なら「上から検索」)
- 「1」＝先頭の項目から検索を実行します。省略した場合は「1」を入力したことになります。
- 「-1」＝末尾の項目から逆方向に検索を実行します。
- 「2」＝先頭の項目から高速な検索を実行します。昇順で並べられていない場合、エラーとなり無効な結果となります。
- 「-2」＝末尾の項目から高速な検索を実行します。降順で並べられていない場合、エラーとなり無効な結果となります。
- 上から検索するか、下から検索するか選べます(空白なら「上から検索」)
「一致モード」と「検索モード」は少々むずかしいので、特に覚える必要はありません。普段は省略して使用して構いません。どうやら実務で使用することもあまり無いようです。
XLOOKUP関数の使い方
XLOOKUP関数は簡単に説明しますと、指定された範囲で、ある特定の行もしくは列のデータを検索し、その値に対応する別の行もしくは列のデータを取得して表示を行う関数です。
詳しくは実際にXLOOKUP関数を使いながら覚えていきましょう。
まず、使用する表を用意します。これはおまけで紹介させていただいているテキストの実践練習問題1に出てくるデータを使用しています。
<表1>
<表2>
検索値を指定する
セルに「=XL」と入力し、TABキーを押すと「=XLOOKUP(」と自動入力されるので、検索値であるC13を指定します。選択できたら、カンマで区切ります。
=XLOOKUP(C13,
検索範囲を指定する
検索範囲は、シート「コード表」の「コード」になりますので、C5からH5を選択します。このときF4を押して絶対参照にしておくことを忘れないようにしましょう。選択したら、忘れずにカンマを入力します。
=XLOOKUP(C13,コード表!$C$5:$H$5,
戻り範囲を指定する
戻り範囲は、品名を調べたいのでシート「コード表」の「品名」の部分（C6からH6）を選択します。このときもF4を押して絶対参照にしておくことを忘れないようにしましょう。選択したら、忘れずにカンマを入力します。
=XLOOKUP(C13,コード表!$C$5:$H$5,コード表!$C$6:$H$6,
見つからない場合を指定する
データが見つからない場合の設定です。今回は空白としたいので「""」を入力します。今回は、一致モードと検索モードを指定しないため、「)」で閉じてしまいます。この項目は省略可です。
=XLOOKUP(C13,コード表!$C$5:$H$5,コード表!C6:$H$6,"")
一致モードを指定する
今回は省略しています。
検索モードを指定する
今回は省略しています。
XLOOKUP関数の特長（VLOOKUP関数・HLOOKUP関数との比較）
特長1．検索範囲の自由度が高い
検索値がVLOOKUP関数では検索範囲の左端を、HLOOKUP関数では上端である必要がありましたが、XLOOKUP関数ではそういった縛りがなく、任意の場所を検索範囲として指定することができます。
特長2．行や列の増減の影響を受けない
VLOOKUP関数では列番号を、HLOOKUP関数では行番号を指定する必要がありましたが、XLOOKUP関数では必要な範囲のみを直接指定するので、列・行の追加や削除があっても関数の再設定を行う必要がありません。
特長3．複数列のデータ取得が簡単（スピル機能）
XLOOKUP関数では、検索値に該当する複数の行や列の結果を一気に取得することができます。これはExcel2021から実装されたスピル機能によるもので、最近のExcelでは重要な概念となっています。
特長4．データが見つからない場合の処理ができる
これはVLOOKUP関数にも、HLOOKUP関数にもなかった機能で、VLOOKUP関数とHLOOKUP関数では見つからなかった場合の処理をIF関数と組み合わせて使用する必要がありました。
しかし、XLOOKUP関数では、検索値が見つからなかった場合の処理は関数の中で、直接指定することができるようになりました。
最後に
いかがでしたか？
XLOOKUP機能は使いこなせば大変便利なことをやってのける関数ですので、ぜひこの機会に使い方をマスターしていってください。
なにか足りないことがございましたら、コメント欄にご意見いただけますと幸いです。
ではでは、参考までに。
おまけ
サーティファイの Excel®表計算処理技能認定試験1・2級を受験するにあたり、これさえ買っておけば必ず受かるといっても過言ではない一冊の本をオススメ致します。
それがこちら。
ポチップ
私も購入させていただき、大変お世話になっております。
以下、参考にしたサイトじゃ。
参考サイト
XLOOKUP関数の使い方、具体例と実践方法
【完全移行でOK？】XLOOKUP関数の書き方|便利すぎる次世代関数の魅力と注意点まとめ
業務効率を大幅アップ! Excel XLOOKUP 関数の使い方早わかり解説 - Microsoft for business
コメント