「VLOOKUPで長年やってきたのに、今さら新しい関数を覚える必要ある？」
正直なところ、私自身も最初はそう思っていました。VLOOKUPで困ったことはあっても、わざわざ使い慣れた関数を変える理由が見つからなかったからです。ところが、VLOOKUPの「検索する列は必ず左端」という制約に何度もぶつかるうちに、XLOOKUPという選択肢があることを知り、実際に使ってみると想像以上に快適でした。
XLOOKUPは、VLOOKUPの後継として登場した比較的新しい検索関数です。今回は基本の書き方から、VLOOKUPとの違い、そして「結局どっちを使えばいいのか」という判断基準まで、実務目線で整理していきます。
1. XLOOKUP関数とは
XLOOKUP関数は、指定した値を検索範囲から探し、対応するデータを別の範囲から取得する関数です。書式は次の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合)- 検索値：検索したい値を指定します。
- 検索範囲：検索対象となる範囲を指定します。
- 戻り範囲：検索結果として取得したい値がある範囲を指定します。
- 見つからない場合：検索値が見つからなかったときに表示する値を指定します（省略可）。
たとえば、A列に商品コード、B列に商品名が入っている表から、指定した商品コードに対応する商品名を取得したい場合は次のように書きます。
=XLOOKUP(D1, A2:A100, B2:B100, "該当なし")D1に入力した商品コードをA2:A100の中から検索し、見つかった行に対応するB列の商品名を返します。見つからなかった場合は「該当なし」と表示されます。VLOOKUPと似た使い心地でありながら、引数の並び方や既定の挙動にいくつかの違いがあるため、次の章でその違いを具体的に見ていきます。
2. VLOOKUP関数との3つの違い
2-1. 検索する列が左端でなくてもよい
VLOOKUP関数では、検索したい値の列が、取得したい値の列より必ず左側になければいけませんでした。XLOOKUP関数は検索範囲と戻り範囲を別々に指定するため、この制約がありません。「B列の商品名からA列の商品コードを取得したい」といった、VLOOKUPでは工夫が必要だった検索が、XLOOKUPならそのまま書けます。
=XLOOKUP(D1, B2:B100, A2:A100)2-2. 列を挿入しても列番号を数え直す必要がない
VLOOKUP関数は「テーブル範囲の中で何列目か」を数字で指定する必要があり、表に新しい列を挿入すると、その数字がズレて参照先が変わってしまう事故がよく起こりました。XLOOKUP関数は戻り範囲を列単位で直接指定するため、この種の事故が起きにくくなっています。
2-3. 検索方法の指定を忘れても近似一致で暴走しない
VLOOKUP関数は、検索方法の引数を省略すると自動的に近似一致（TRUE）として扱われ、意図しないデータを拾ってしまう事故につながりやすいという弱点がありました。XLOOKUP関数は、既定の動作が完全一致になっているため、書き忘れによる近似一致の事故が起きにくい設計になっています。
3. 実務でよくある使用例
3-1. 社員番号から氏名・部署をまとめて取得する
=XLOOKUP(F1, A2:A200, B2:B200)社員番号を1つ入力するだけで、氏名や部署など複数の情報を取得したい場合、戻り範囲を複数列にまとめて指定することで、1つの数式でスピル（複数セルへの自動展開）させて表示することもできます。
=XLOOKUP(F1, A2:A200, B2:D200)3-2. 完全一致するデータがなければ近い順位のデータを返す
=XLOOKUP(G1, H2:H50, I2:I50, "該当なし", -1)第5引数に-1を指定すると、完全一致するデータがない場合に「検索値未満で最も近い値」を返してくれます。価格帯や点数の範囲によって評価を振り分けたいときなど、VLOOKUPで近似一致（TRUE）を使っていた場面の代わりに使えます。
3-3. 検索範囲を下から探す
=XLOOKUP(J1, K2:K500, L2:L500, "該当なし", 0, -1)第6引数に-1を指定すると、検索範囲の下（末尾）から検索を始めます。同じ商品コードが複数回登場する売上履歴のようなデータで、「一番新しい（一番下にある）記録を取得したい」というときに便利です。VLOOKUPでは実現しづらかった検索方向の指定が、XLOOKUPでは引数1つで可能になります。過去の全履歴の中から最新の状態だけを追いかけたい、という実務のニーズに、これまでは補助列や並び替えで対応していた方も多いのではないでしょうか。
3-4. INDEX+MATCHの代わりとして使う
これまでVLOOKUPの弱点を補うためにINDEX関数とMATCH関数を組み合わせて使っていた場面も、XLOOKUP関数1つで書き換えられることがよくあります。
=INDEX(D1:D100, MATCH(A1, B1:B100, 0))上記のようなINDEX+MATCHの組み合わせは、次のようにXLOOKUP1つで表現できます。
=XLOOKUP(A1, B1:B100, D1:D100)2つの関数を組み合わせる必要がなくなる分、数式が短くなり、後から見直す人にとっても理解しやすくなります。
3-5. スピル機能と組み合わせて複数行を一気に検索する
=XLOOKUP(F2:F10, A2:A200, B2:B200)検索値をF2:F10のように複数セルの範囲で指定すると、対応する結果が自動的に複数のセルに展開されます（スピル）。1件ずつ数式をコピーしていく必要がなく、まとめて検索結果を一覧表示したいときに便利です。複数の商品コードから商品名を一括で調べたいときや、複数の社員番号から氏名を一覧化したいときなど、VLOOKUPでは1件ずつ数式をコピーする必要があった作業を、まとめて一度に処理できるようになります。
4. XLOOKUP関数でよくある間違い
4-1. 検索範囲と戻り範囲の行数が一致していない
検索範囲をA2:A100にしたのに、戻り範囲をB2:B90のように短く指定してしまうと、エラーになります。VLOOKUPよりもエラーとして気づきやすい点ではありますが、範囲を指定する際は2つの範囲の行数を必ず揃えることを意識しておく必要があります。
4-2. 「見つからない場合」の引数を省略してエラー表示のままにする
第4引数を省略すると、検索値が見つからなかった場合に「#N/A」というエラーがそのままセルに表示されます。上司や取引先に提出する資料でこの状態のまま放置すると、見た目の印象がよくありません。「該当なし」や空欄など、何を表示するかをあらかじめ決めて指定しておくことをおすすめします。
4-3. 古いバージョンのExcelでも使えると思い込んでしまう
XLOOKUP関数は、Microsoft 365やExcel 2021以降でしか使用できません。共有相手が古いバージョンのExcelを使っている場合、XLOOKUPで作った数式はエラーになってしまいます。社内で共有するファイルにXLOOKUPを使う前に、関係者が使っているExcelのバージョンを確認しておくと安心です。バージョンが混在する環境では、XLOOKUPを使う前に「ファイルを開く全員が対応バージョンを使っているか」を確認する一手間が、後々のトラブルを防いでくれます。
4-4. VLOOKUPと混在させて表の管理が複雑になる
一部の数式はVLOOKUP、一部はXLOOKUPというように、同じ表の中で新旧の関数が混在していると、後から見直す人にとって分かりづらい表になってしまいます。新しく作る表ではXLOOKUPに統一する、既存の表は無理に書き換えないなど、切り替えの方針をあらかじめ決めておくと、表全体の一貫性を保ちやすくなります。
4-5. スピルした範囲に誤って別のデータを入力してしまう
スピル機能で複数セルに結果が自動展開されている状態で、その範囲の途中のセルに別の値を直接入力してしまうと、スピルが正しく機能しなくなり「#SPILL!」というエラーが表示されます。スピルする数式の周辺には、あらかじめ他のデータを置かないようにレイアウトを工夫しておくと、このエラーを避けられます。
5. 結局、VLOOKUPとXLOOKUPどちらを使えばいい？
すでにVLOOKUPで問題なく運用できている表を、無理にXLOOKUPへ書き換える必要はありません。ただし、これから新しく作る表や、検索する列が左端に来ない構造の表では、最初からXLOOKUPを使っておく方が、将来の列挿入や検索方向の変更に強くなります。VLOOKUPの基本的な考え方や、近似一致の危険性、重複列による事故など、検索系関数に共通する注意点については、以下の記事でも詳しく取り上げています。
【関数の使い方シリーズ④】VLOOKUP、その検索結果、本当に合っていますか？ https://www.excel-laboratory.online/vlookupkansu/
よくある質問
Q. XLOOKUPはVLOOKUPより計算が重くなりませんか？
一般的な実務レベルのデータ量（数千〜数万行程度）であれば、体感できるほどの速度差はほとんどありません。数十万行を超えるような大規模なデータを扱う場合は、検索範囲を必要最小限に絞るなど、どちらの関数を使う場合でも共通の工夫が有効です。列全体（A:A）を検索範囲に指定するクセがある場合は、この機会に具体的な行数で範囲を絞る書き方に見直しておくと、ファイルの動作も軽くなります。
Q. INDEX関数とMATCH関数の組み合わせは、もう使わなくてよいですか？
XLOOKUPが使えるバージョンであれば、多くの場面でINDEX+MATCHの代わりにXLOOKUPを使えます。ただし、XLOOKUPが使えない環境と共有するファイルでは、引き続きINDEX+MATCHの組み合わせが必要になるため、両方の考え方を知っておくと安心です。
Q. XLOOKUPで複数条件を検索することはできますか？
検索範囲を工夫することで対応できます。たとえば「部署」と「氏名」を1つの検索値としてつなげた補助列を作り、その補助列を検索範囲にする方法があります。複数条件の検索を頻繁に行う場合は、この補助列を使う方法を覚えておくと応用が利きます。
Q. XLOOKUPとVLOOKUPを同じファイルの中で両方使っても問題ないですか？
動作としては問題なく共存できます。ただし、前述の通り、同じ表の中で新旧の関数が入り混じっていると、修正するときにどちらの関数を使うべきか毎回迷う原因になります。ファイル単位、あるいはシート単位で「ここから先はXLOOKUPに統一する」というようにルールを決めておくと、引き継ぎのときにも迷いにくくなります。
まとめ
XLOOKUP関数は、VLOOKUP関数の弱点だった「検索列が左端固定」「列挿入でズレる」「近似一致で暴走しやすい」という3つの問題を解消した検索関数です。
- 検索範囲と戻り範囲を別々に指定できるため、検索列の位置を選ばない
- 列を挿入しても参照がズレにくい
- 既定が完全一致のため、書き忘れによる事故が起きにくい
すべての表を今すぐ書き換える必要はありませんが、「これから作る表」「検索列が左端に来ない表」から少しずつ取り入れていくと、無理なく移行できます。使えるバージョンかどうかだけは、事前に確認しておきましょう。VLOOKUPを否定する必要はまったくありません。長く使われてきた分、ネット上の情報も豊富で、多くの人がすでに使い慣れている関数です。XLOOKUPは、その延長線上にある「もう一つの選択肢」として、状況に応じて使い分けていくのがちょうどいい距離感だと思います。
コメント