XLOOKUP 関数は、表や範囲から行ごとに情報を検索します。1つの列で検索語を検索し、戻り列がどちら側にあるかに関係なく、別の列の同じ行から結果を返すことができます。
XLOOKUP関数が使えるのは、サブスクリプション版の「Microsoft 365」アプリの「Excel for Microsoft 365」と永続ライセンス版の最新版 Excel 2021です。Excel 2016やExcel 2019では使用できませんが、今のうちに是非知っておいた方がよいと思います。
VLOOKUP関数と一文字違いだからきっと似ているんですよね。どこが違うのかしっかり勉強したいと思います。よろしくお願いします(^^)/
【この記事でわかることは】
・XLOOKUP とはどういう関数なのかを解説
・XLOOKUP 関数の使用例（スピル機能を含む）について
XLOOKUP 関数の構文
XLOOKUP 関数の「数式オートコンプリート」では次のように表示されます。範囲または配列を検索し、一致に対応する項目を2つめの範囲または配列から返します。既定では完全一致が使用されます。
XLOOKUP 関数の構文は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
※[ ]の引数はオプションです。それ以外の引数は必須です。
引数を確認します
| 引数 | 内容 |
|---|---|
| 検索値 | 【必須】検索する値を指定します。大文字/小文字、全角/半角は区別されません。 |
| 検索範囲 | 【必須】検索する配列または範囲を指定します。 |
| 戻り値範囲 | 【必須】検索結果を返す範囲または配列を指定します。［検索値］が［検索範囲］の中で 見つかった場合、その行位置にある［戻り値範囲］の値が返されます。 |
| [見つからない場合] | ［検索値］が［検索範囲］の中で見つからなかった場合に返す値を指定できます。 省略すると、見つからなかった場合には［#N/A］エラーが返されます。 |
| [一致モード] | 一致の種類を以下の値で指定します。 0または省略 = 完全一致 【規定値】 -1 =［検索値］の次に小さい値 1 =［検索値］の次に大きい値 2 =［検索値］ワイルドカード（ *、?、および 〜）との一致 |
| [検索モード] | 使用する検索モードを以下の値で指定します。 1 または省略 = 先頭の項目から末尾へ検索 【規定値】 -1 =［検索値］末尾の項目から逆方向に検索 2 =［検索値］昇順で並べ替えられた範囲をバイナリ検索 ※並べ替えられていない場合、無効な結果が返されます。 -2 =［検索値］降順で並べ替えられた範囲をバイナリ検索 ※並べ替えられていない場合、無効な結果が返されます。 |
VLOOKUP関数と違う部分は、引数の「検索範囲」です。VLOOKUP関数では「戻り値範囲」までを含めた「表全体」を指定しました。そして「検索値」は指定した表全体の一番左端列に無ければいけないという仕様です。XLOOKUP関数の場合は「検索範囲」の位置は自由に指定できるという点で汎用性が非常に高くなっています。
XLOOKUP 関数の使用例
XLOOKUP 関数は、VLOOKUP 関数の上位強化版といわれています。
それでは、使用例をいくつか見ていきましょう。
完全一致検索の使用例
完全一致検索の使用例を二つ紹介します。
百名山リストから山の名前を取り出す（完全一致検索）
日本百名山のリストを使っていきます。データ量は百名山なので当然100行あります。
引数は「検索値」「検索範囲」「戻り値範囲」の必須項目と [見つからない場合]に”該当なし”だけ指定しています。それ以外の引数オプションは省略しているので規定値となっています。
G2セルに「番号1～100」を入力するH2セルにと山の名前が検索表示されます。
VLOOKUP関数で同じことをする場合はこんな感じでIFERRORと位置指定が必要
=IFERROR(VLOOKUP(G2,A2:B101,2,FALSE),”該当なし”)
「検索範囲」左側でなくてもOK
最初の例に使った画像では「検索範囲」が一番左にありますが、位置を移動させてみます。
A列からC列に移動しましたが、ちゃんと検索できていることが確認できます。
これと同じことは、VLOOKUP関数ではできませんね(^^;
次のようにINDEX関数+MATCH関数を組み合わせる方法なら可能ですね
=INDEX(A2:C101, MATCH(G2, C2:C101, 0),1)
番号入力で情報を一連で取り出す（完全一致検索スピル抽出）
「戻り値範囲」を広げれば、次のように「スピル」して一連で取り出すことができます。
ここで「戻り値範囲」をA列も含めて指定した場合、戻り値はA列～E列までの全部が帰ってくることになります。
近似値検索の使用例
入力値以下の最大値を検索する（近似値検索）
まずは下の動画をご覧ください。
[一致モード] を -1 ([検索値]の次に小さい値)としています。
入力した値の近似値の最大を検索した結果をスピル表示しています。表示されたデータの標高と比べてみてください。
入力値より大きい最小値を検索する（近似値検索）
[一致モード] を 1 ([検索値]の次に大きい値)としています。
入力した値より大きい近似値の最小で検索した結果をスピル表示しています。表示されたデータの標高と比べてみてください。標高が1,630ｍ と 1,661m の間には百名山が無いことがわかります。
まとめ（おわりに）
以上、XLOOKUP 関数の使用例の一部を解説しました。すでに使用できるバージョンのExcelを使っているユーザーのみなさんは、いろいろ試して使い方に慣れておきましょう。職場はまだ対応していないけど自宅では対応しているというユーザーが増えてきているのではないでしょうか。
XLLOOKUP 関数は、VLOOKUP 関数と比べても引数の指定方法が分かりやすいし、検索範囲が左端でなくてもOKな点や強力な「スピル」機能によって複数のセルに計算結果を表示できるのはものすごく画期的です。この機能を使いこなせるようにしておきましょう(^^)/
XLOOKUP関数すごいです！ VLOOKUP関数と比較するのはなんだか違う気がします(^^ゞ
しっかり勉強して使いこなせるようにしておきたいと思います。それにしても「スピル」機能って便利ですね(^^)/
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
