【Excel】XLOOKUP関数はVLOOKUPの進化版、なんてもんじゃない★
こんにちは、HARUです！
今回は、実務で出くわす機会の多い「検索関数」の使い方を演習していきます。
一言で「検索関数」といっても、内容が盛り沢山かつ順をおってご説明した方が理解が深まりやすいので、以下のスリーステップに分けて段階的に投稿していきます。
①VLOOKUP関数
②INDEX関数・MATCH関数
③XLOOKUP関数
この記事では前回までのVLOOKUP関数、INDEX関数・MATCH関数に続けて、「XLOOKUP関数」を解説します。
VLOOKUP関数や、INDEX関数・MATCH関数の組み合わせでは実現できなかったエラー対策、複数条件検索に加えて、Excelの新常識「スピル」についても触れていきます。
なおXLOOKUP関数はMicrosoft365、Excel2021のバージョンで使える比較的新しい関数です。
該当のバージョンの普及率が全体の３割程度と言われていますので、しばらくVLOOKUP関数に出くわす頻度が高い状況は続きますが、近い将来、XLOOKUP関数がビジネスの現場でスタンダードになっていきますので、今のうちにおさえておきましょう！
↓前回までの記事はこちらから↓
XLOOKUP関数の構成
サンプルは前回のINDEX関数・MATCH関数で使用した表です。
商品名を検索値として、該当の管理番号を取得します。
"=xl"と入力したら、XLOOKUP関数が候補として表示されます。
ここでXLOOKUPが出てこなければ、残念ながらXLOOKUP関数が使えないバージョンです。
Tabキーを押して関数を挿入します。
第１引数「検索値」はVLOOKUP関数やMATCH関数といったその他の検索関数の第１引数と同じです。
今回は商品名が検索値なので、I3セルを参照します。
第２引数「検索範囲」には、第１引数「検索値」が含まれる範囲を参照します。
今回は商品名をサーチしたいので、マスターにおける商品名の列を選択します。
第３引数「戻り範囲」には、取得したいデータが含まれる範囲を参照します。
今回は管理番号を返したいので、マスターにおける管理番号の列を選択します。
残りの３つの引数は任意設定です。
後ほど詳しく触れていきますので、まずはこの状態で結果を見てみます。
商品Eの管理番号が抽出されます。
VLOOKUP関数では検索範囲全体を指定して、その中から何列目にある情報を取得するといったサーチをかけました。
検索値が検索範囲の中で一番左になくてはならず、検索値の右側の情報しか取り出せないんでしたね。
前回はINDEX関数とMATCH関数を組み合わせることで、この課題を解消しました。
XLOOKUP関数の場合、今回のように第３引数「戻り範囲」を第２引数「検索範囲」よりも左側のエリアに設定すれば、容易に検索値の左にあるデータを取得できます。
商品名を起点に単価を返したいとなったときは、第３引数「戻り範囲」を該当列にスライドさせるだけです。
これを1つの関数で完結できるのはありがたいですよね。
横方向の検索（HLOOKUPの代用）
データを縦方向に検索するVLOOKUP関数に加えて、横方向に検索するHLOOKUP関数という検索関数があります。
垂直に探すVertical Lookupに対し、水平に探すHorizontal Lookupです。
たとえば先ほどのマスターデータの行列を入れ替えた表があったとして、指定商品の単価を取り出していきます。
関数の基本構成はVLOOKUP関数と同じです。
HLOOKUP関数を挿入し、第１引数「検索値」に指定商品名のセルを参照します。
第２引数「範囲」は商品名の行が一番上にくるように参照します。
第３引数「行番号」には、第２引数「範囲」の中で単価情報が位置する上から"5"行目を入力します。
第４引数「検索方法」には、完全一致の「0」を入力します。
結果、商品Eの単価"11,100"円が取得できます。
XLOOKUP関数は、縦方向に加えて横方向の検索も可能です。
XLOOKUP関数を挿入したら、
第１引数「検索値」に商品名を参照します。
第２引数「検索範囲」には商品名の範囲を参照します。
第３引数「戻り範囲」には単価の範囲を参照します。
検索範囲も戻り範囲も、選択方向が横向きになっただけですね。
結果として、同じ単価情報が正しく取得できます。
Excelのワークシートが最大列数よりも最大行数の方が多い仕様になっている通り、このように横に広がった表に出くわす機会はほとんどありません。
そのため、単なるHLOOKUP関数の代替としての横方向検索で使うことはめったにないですが、次のStepで解説する新機能と組み合わせることでより便利にお使いいただけます。
ここでは検索範囲を横方向に選択できることだけ、おさえておきましょう。
Excelの新常識「スピル」
マスターデータと検索結果の抽出先の見出しが間髪いれずに同じ順序で並んでいる場合、VLOOLUP関数では以下のように入力していました。
ただ、この数式をそのまま右へコピーすると、すべての欄に商品名が返ってしまいます。
これは、第３引数「列番号」に入力した固定値、今回の場合は"2"がそのまま複製されるためです。
こんなシーンでは、登録時期は３列目、担当者は４列目といったように、１つずつ修正する必要があります。
VLOOKUP関数の講座ではMATCH関数やCOLUMN関数を活用して自動調整しましたが、極力シンプルな数式で済ませたいところです。
ここで、XLOOKUP関数を挿入します。
第１引数「検索値」には管理番号のセルを参照し、第２引数「検索範囲」は管理番号が含まれる列を参照します。
そして、今回は第３引数「戻り範囲」の選択の仕方がポイントです。
これまでの単一列や単一行選択ではなく、情報を取得したい数だけ複数列にまたがって参照します。
結果を見てみると、関数を入力したのはB2セルだけなのに、それ以外の欄にもデータが取得されています。
数式バーをみてみると、自動反映されたC列以降のセルは、薄い文字で表示されています。
これがExcelの新常識、「スピル」という機能です。（spill：あふれる、こぼれる）
Excelにおいては動的配列数式と言って、条件を満たす数式を一度インプットしてしまえば、連動して該当範囲にも自動入力される画期的なアイテムです。
これによって、VLOOKUP関数を右へコピーするときに必要だった、検索値の複合参照や、参照範囲の絶対参照も不要ですし、列番号の設定や自動調整の手間もかかりません。
今回はXLOOKUP関数の第３引数「戻り範囲」をB列からG列まで選択したので、C列以降、G列まで自動入力されています。
これをたとえばD列までに変更した場合はスピルの範囲がD列で止まります。
なお、スピルで自動入力されるはずの範囲に何らかのデータが入っていると、関数をインプットしたセルにスピルエラーが返ります。
スピルで数式を自動反映させる範囲は、すべて空白セルにしておく必要があることをおさえておきましょう。
列単位でデータ抽出
実際の業務では、縦にも横にも膨大なデータ量の表を扱うことがほとんどです。その中から、特定の情報を列単位で取り出したいことがよくあります。
たとえば、マスターから商品名と単価のデータだけ抜き出す場合、ここまで演習してきたXLOOKUP関数の特長が効力を発揮します。
XLOOKUP関数を挿入し、第１引数「検索値」には商品名と入力されたI4セルを参照します。
第２引数「検索範囲」には、その商品名の文字列が含まれる見出しの範囲を横方向に参照します。
後ほどこの数式を単価の欄にコピーしますので、絶対参照にしておきます。
第３引数「戻り範囲」では、第２引数「検索範囲」と同じ列数分、すべての範囲を選択します。こちらも絶対参照にしておきます。
結果、商品名の列の情報が第３引数「戻り範囲」から取得できます。
この数式を右へコピーすると、単価の列も抽出できます。
これは、XLOOKUP関数がHLOOKUP関数を代用できることと、スピル機能がポイントです。
検索値をまず横方向に検索し、その検索値を見つけたら情報を縦に取りに行きます。そこにスピルが発動し、今回は縦方向にデータを一括取得できたということです。
実務で膨大なデータを扱われている方なら、指定した見出しの情報だけを列単位で取り出して、隣接して並べられることの有用性をご理解いただけるかと思います。
複数条件検索
VLOOKUP関数やINDEX関数・MATCH関数では１つの検索値しか指示できませんでしたが、XLOOKUP関数は複数の検索条件を設定できます。
たとえば商品別、向け先別、ロット別に異なる輸送単価をまとめた一覧表があったとします。
それぞれ条件を変えて欄外に輸送単価を取り出してみます。
（各条件はドロップダウンリストから選択します）
XLOOKUP関数を挿入し、第１引数「検索値」を参照します。
複数条件で検索するときは、下図のように参照セルを"&"(アンパサンド)でつなげて指示します。
第２引数「検索範囲」も第１引数「検索値」と同じ要領で参照します。
商品名の範囲、出荷先の範囲、ロットの範囲を"&"でつなげます。
第３引数「戻り範囲」には、輸送単価の範囲を参照します。
結果、商品Aが東京方面に100ロット出荷された場合の輸送単価は11,000円と返されます。
これがたとえば、商品Bが名古屋方面に50ロット出荷されれば輸送単価は29,000円となります。
商品Cが大阪方面に100ロット出荷されれば輸送単価は58,000円となります。
このようにXLOOKUP関数では、複数の検索値をもとにデータを取得できるのです。
エラー対策
ここから、XLOOKUP関数の任意設定の引数を解説します。
たとえば商品名の欄に、マスターに存在しない商品Dを入力してみます。
該当のデータがヒットしないので、エラーが返ります。
VLOOKUP関数やINDEX関数＆MATCH関数では、こうしたエラー対策としてIFERROR関数でネストする必要がありました。
XLOOKUP関数であれば、第４引数「見つからない場合」に、たとえば””(ダブルコーテーションマーク)で何も囲まず空白を返したり、この中に"該当なし"と入力したりして、該当データが存在しないことを表現できます。
エラーへの対応も、他の関数を組み合わせることなくXLOOKUP関数だけで完結できてしまうのです。
一致モード
第５引数「一致モード」は、VLOOKUP関数の第４引数「検索方法」と同じ役割です。
実務では完全一致での検索がほとんどですし、省略してもデフォルトで完全一致検索となりますので、特に気にしなくて良い引数です。
ちなみにVLOOKUP関数の近似一致検索は、検索値以下の最大値、要は検索値よりも小さい近似値を返す仕様でした。
取引高に応じた販売奨励金を支払うために、その奨励金率のテーブルを近似一致で検索する、というユースケースをVLOOKUP関数の講座で取り上げました。
取引先Dの取引高は、10,000,000に届いてないけれども、1,000,000は超えているので、SSクラスの1.50％を返すといったイメージです。
XLOOKUP関数の「完全一致または次に小さい項目」と同義です。
さらにXLOOKUP関数であれば、「完全一致または次に大きい項目」の指定も可能で、検索値以上の最小値、要は検索値よりも大きい近似値を返すこともできます。
近似一致検索は他にも、成約件数に応じた歩合の報酬や、一定の就業時間を超えた場合の給与体系などに応用できます。
また、使用頻度はそれほど多くありませんが、ワイルドカードを用いたあいまい検索も可能です。
検索値となるデータ以外にも空白文字や余計な記号などが付着している場合に重宝できます。
以前VLOOKUP関数で半角スペースやシングルコーテーションマークがこびりついたマスターから、必要なデータを検索する際に使った"*"(アスタリスク)などが、XLOOKUP関数にも応用できるのです。
検索モード
VLOOKUP関数に代表される検索関数は原則、上から検索をスタートし、最初に見つかったデータを取り出す仕様になっています。
ただし、情報がどんどん蓄積されていく構造の場合、下から検索をかけないと最新状況が取り出せないケースもよくあります。
たとえば前述で輸送単価を取得した表について、よくみると2022年10月に、商品Bを名古屋方面に出荷する場合の輸送単価が改定されています。
古い単価を抽出してしまわないように、対策が必要です。
こんなときは、第６引数「検索モード」を"末尾から先頭へ検索"にします。
下方向からサーチをかけ、検索条件と最初に合致した22行目のデータ"27,000"円が返されます。
まとめ
以上のように今回はXLOOKUP関数の構成と特長についてご紹介しました。
まずはお勤め先のバージョンを確認していただいて、XLOOKUP関数が使える環境であれば、積極的に実践していきましょう。
一連の検索関数の講座でご紹介した様々な検索方法と関連知識を、ぜひ日々の業務で試してみてくださいね！
↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。
↓↓Excel操作をとにかく高速化したい方へ↓↓