Excelで表を扱うとき、「VLOOKUPは使っているけれど思うように検索できない」「参照範囲を追加するとエラーになって困る」と悩んでいませんか？そんな課題を解決するために登場したのが XLOOKUP関数 です。結論から言うと、XLOOKUPはVLOOKUPよりも柔軟で分かりやすく、今後の業務効率化に大きく役立ちます。
本記事では、まずVLOOKUPとXLOOKUPの違いを整理し、次に「なぜ置き換えるべきなのか」を3つのポイントに分けて解説します。さらに、実務でよくある検索や参照のケースに触れながら、初心者でも理解しやすい具体例を紹介します。この記事を読めば、VLOOKUPに代わる新しい標準として、XLOOKUPを自信を持って使えるようになるでしょう。
Excel XLOOKUPの使い方の基本
XLOOKUPとは？従来の関数との違い
XLOOKUPは、指定した値を縦・横どちらの向きでも探し、同じ行または列の結果を返す最新の検索関数です。VLOOKUPのように「検索列は一番左」「右方向にしか返せない」といった制限がなく、左方向への参照や列の追加・入れ替えにも強い設計になっています。既定で「完全一致」を優先する点、見つからなかった場合の返り値を引数で指定できる点も優れています。
XLOOKUPの基本構文と引数の意味
XLOOKUPの基本構文は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
検索値で探したい値を指定、検索範囲で検索対象の範囲を指定、戻り範囲で戻したい値の範囲を指定します。
[見つからない場合]は、該当データがないときにどういったメッセージや値を返すかを指定することができます。
[一致モード]では完全一致・近似一致・ワイルドカードなどの照合方法を指定し、検索モードでは検索範囲の上から／下からどちらから検索を進めていくか方向を制御できます。
実際の使用例：商品コードから商品名を検索する
A列に商品コード、B列に商品名がある場合、商品コード「E2」に対応する商品名を返す式は次のように書きます。今回、データが見つからない場合は「該当なし」と返すよう指定しています。
=XLOOKUP(E2, A:A, B:B, "該当なし")
上記では範囲を列全体にしていますが、実データの存在する範囲に絞ると計算が速くなります。Excelは参照数・演算回数が多いほど計算コストが増えるため、必要最小限の範囲指定が推奨されています。
VLOOKUPとXLOOKUPの違い
参照範囲の柔軟さ
VLOOKUPは検索列が参照表の最左列である必要があり、列の挿入や順番変更に弱い一方、XLOOKUPは検索範囲と戻り範囲を独立して指定できるため列の位置に依存しません。XLOOKUPの方がより柔軟性が高い関数になっているというのが大きな違いです。
左方向検索の可否
VLOOKUPは最左列しか検索列に指定できないという性質から、検索列の右側にあるデータしか返せませんが、XLOOKUPは検索列の左方向にあるデータも問題なく参照して返すことができます。これにより、表構造を変更してVLOOKUPで使える形に整形する必要がなくなりました。
エラー処理のしやすさ
XLOOKUPは該当データが見つからない場合に[見つからない場合]で戻り値を直接制御できます。VLOOKUPで同様の挙動を実現するにはIFERRORやIFNAで式全体を包む必要があり式が長くなりがちでしたが、XLOOKUPはそれをスマートにフォローしています。
INDEXとMATCHを組み合わせることでXLOOKUPと同様に高い自由度で値検索ができるということで私も活用していましたが、上記の点についてはINDEX MATCHはVLOOKUPのように他関数でエラーを拾う必要があるので、今ではXLOOKUPしか使わなくなりました。
なお、[見つからない場合]は「未検出」のときにだけ働くため、型不一致など別原因のエラーを包括的に扱う目的ではIFERRORの併用が依然として有効になります。ただし、型不一致でエラーになるということは正確な分析集計ができないということなので、基本的には書式設定を整えてXLOOKUPのみで完結する状態にすることを最低ラインとして考えたほうが良いでしょう。ただし、事前にある程度の切り捨ては問題ないとわかっている場合などは関数を組み合わせてエラーをフォローしても問題ないので、そこは柔軟に対応するようにしてもらえればと思います。
VLOOKUPからXLOOKUPに置き換えるべき3つのポイント
ここまで書いてきたことを含めて、VLOOKUPからXLOOKUPに置き換えるべき3つのポイントをまとめます。
①完全一致が前提で誤検出が起きにくい
VLOOKUPの検索方法は、指定しないとデフォルトで近似一致になるため、誤って省略してしまうと思わぬ結果を返すことがあります。それに対してXLOOKUPの[一致モード]はデフォルトが完全一致のため、深く考えずに使用した際にエラーになる可能性が低くなります。
②高い柔軟性
XLOOKUPは左方向を含む柔軟な参照指定ができ、表構造を変更する必要がないため保守が容易になります。また、VLOOKUPの検索方法は近似一致 or 完全一致の2択ですが、XLOOKUP関数では完全一致のほかに、完全一致データがない場合に次に小さい・大きい値を返す（それぞれデータが昇順・降順に並んでいることが前提）、ワイルドカードによる部分一致指定とできることが多くなっています。
③[見つからない場合]や検索方向の制御などオプションが充実している。
どちらも他関数を組み合わせて使用することで再現できていましたが、その分コードが長くなってしまい保守が面倒になるため、やはり1つの関数だけでこれらをシンプルに表現できるという点でXLOOKUPは非常に優れています。
Excel XLOOKUPの便利な活用シーン
部分一致検索の方法
品名の一部しか分からない場合は、[一致モード]を「2（ワイルドカード）」に設定し、「*」「?」「~」を活用します。たとえば「H2に含まれる語を含む品名」を探して値を返したいときは次のように書きます。
=XLOOKUP("*"&H2&"*", 品名列, 返す列, "該当なし", 2)
XLOOKUPはワイルドカードをネイティブにサポートしているため、部分一致の照合が非常に簡単です。
複数条件での検索方法
部門と社員IDの両方が一致する行からメールアドレスを返す、といった複数条件も配列計算で実現できます。
=XLOOKUP(1, (部門列=H2)*(社員ID列=H3), メール列, "該当なし")
条件式の真偽を掛け合わせて1になる行を探す発想でシンプルに表現することができます。配列を使った複数条件の手法は実務でも広く使われるので、覚えておくと便利です。
縦横どちらにも対応できる検索
月別（行）×商品別（列）のような二次元表では、XLOOKUPを入れ子にして「縦方向の一致」と「横方向の一致」を組み合わせることで、クロス集計表の特定セルを返すことができます。
=XLOOKUP(返したい月の値, 月の見出し行範囲, XLOOKUP(返したい商品の名称, 商品の見出し列範囲, データ範囲), "該当なし")
XLOOKUP使用時の注意点とエラー対策
よくあるエラーと原因
検索値の書式設定が異なる（数値と文字列の不一致）、検索範囲と戻り範囲のサイズが合っていない、参照範囲が過剰に広い、といった要因で#N/Aや#VALUE!が発生します。
精度を上げるため、上記については重ねて点検をすることをお勧めします。ただし、それらを切り捨てても問題ないと事前にわかっている場合については、[見つからない場合]を設定してフォローしても問題はないと思います。
IFERROR関数との組み合わせ方
[見つからない場合]で「未検出」だけをメッセージに置き換えつつ、その他の予期せぬエラーを包括的に扱いたい場合は、次のようにIFERRORで式全体を包みます。
=IFERROR( XLOOKUP(E2, A:A, B:B, "該当なし"), "計算エラー" )
このように役割を分けると、原因に応じたメッセージが返せます。
ただし、先に述べているようにこれは集計の正確性に関わってくるので、IFERRORでキャッチするだけで問題ないものかどうか吟味は十分するようにしてください。
大規模データでのパフォーマンス対策
XLOOKUPは柔軟ですが、不要に広い参照や複雑な入れ子は計算コストが高くなります。実データの範囲に参照を限定する、ヘルパー列を作成して中間計算を事前に済ませることで計算回数を削減するといった基本に立ち返るだけで体感速度は改善します。
Microsoftも「重複・反復・不要計算の削減」「より効率的な関数の選択」など、式と参照の最適化を推奨しています。必要に応じて計算方向や近似一致の活用も検討すると良いかと思います。
まとめ｜XLOOKUPを使いこなして業務を効率化しよう
VLOOKUPからのスムーズな移行のコツ
まずは既存のVLOOKUPを、同じ入出力になるようXLOOKUPで書き換えるところから始めます。完全一致を既定とする点、左方向参照に強い点、未検出時の返り値を指定できる点を意識して取り組めばスムーズに移行できると思います。
今後のExcel業務でXLOOKUPを習得すべき理由
データ検索の要件は多様化であり、クロスセル表からのデータ検索や部分一致、末尾優先など、突き詰めていくと複雑な要件が出てきます。XLOOKUPはこうした複雑な要件を標準機能だけで満たしており、作りがシンプルになるためエラー処理や保守が容易になります。そのため、表構造の変更にも柔軟に対応しやすく、トラブルの少ないワークブック運用につながります。
関数単体で見たときにVLOOKUPに比べて少し複雑になってしまいますが、総合的に考えると明らかにXLOOKUPの方がシンプルで分かりやすくなるので、ぜひ一度使ってみてください。
参考
他にも色々なExcelの機能を紹介しています。以下の記事から各リンクに飛んでいけるので、ブックマークしておくと便利です。
コメント