前の職場で在庫管理表を任されていたとき、商品名の列がいちばん右端にある表と、何年も付き合っていました。商品コードから商品名を引きたいのに、VLOOKUPは「検索する列より右側しか値を取れない」というルールがあるため、そのままでは使えません。仕方なく、商品名の列を検索用にコピーして左端に貼り付け直す作業を、月に20件前後の商品追加のたびに繰り返していました。1件あたり数分の作業でも、月末には合計1時間近くになっていたと思います。
「べつにこれくらい我慢すればいいか」と思っていたのですが、あるときXLOOKUPという関数の存在を知り、試しに使ってみたところ、その貼り付け直しの作業がまるごと不要になりました。今回は、私と同じように「今さら新しい関数を覚えるのは面倒」と感じている方に向けて、XLOOKUPの基本と、VLOOKUPとの違い、そして実務でどちらを使うべきかの判断基準をまとめます。
1. あなたの表、こんな症状に心当たりはありませんか？
本題に入る前に、まずは自分の表がXLOOKUPで楽になるタイプかどうかをチェックしてみてください。以下のうち1つでも当てはまれば、この先の内容がそのまま役立つはずです。
- 検索したい値の列が、取得したいデータの列より右側にある
- 表に新しい列を挿入するたびに、VLOOKUPの列番号がズレて数式が壊れる
- 検索方法の引数（TRUE/FALSE）を書き忘れて、見当違いの値が返ってきたことがある
- 同じ商品コードが複数回登場するデータから、一番新しい記録だけを取り出したい
- 1つのキーから、氏名・部署・内線番号など複数の情報をまとめて取得したい
これらはすべて、VLOOKUPの構造的なクセに起因する「あるある」です。次の章から、なぜこうした症状が起きるのか、そしてXLOOKUPがどう解決してくれるのかを順番に見ていきます。
2. XLOOKUP関数の基本の書き方
XLOOKUP関数は、指定した値を検索範囲の中から探し、対応するデータを別の範囲から取得する関数です。書式は次の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合)- 検索値：検索したい値を指定します。
- 検索範囲：検索対象となる範囲を指定します。
- 戻り範囲：検索結果として取得したい値がある範囲を指定します。
- 見つからない場合：検索値が見つからなかったときに表示する値を指定します（省略可）。
たとえば、A列に商品コード、B列に商品名が入っている表から、指定した商品コードに対応する商品名を取得したい場合は、次のように書きます。
=XLOOKUP(D1, A2:A100, B2:B100, "該当なし")D1に入力した商品コードをA2:A100の中から検索し、見つかった行に対応するB列の商品名を返します。見つからなかった場合は「該当なし」と表示されます。見た目の使い心地はVLOOKUPとよく似ていますが、引数の並び方や既定の挙動にいくつか重要な違いがあります。
3. VLOOKUPとXLOOKUP、何がどう違うのか一覧で比較
冒頭のチェックリストで挙げた症状は、次の3つの違いに集約されます。表にまとめると次の通りです。
| 項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 検索する列の位置 | 取得したい列より必ず左側 | 制約なし（右にも左にも検索可） |
| 列を挿入したときの挙動 | 列番号がズレて参照が壊れやすい | 列単位で範囲指定するためズレにくい |
| 検索方法を省略した場合 | 近似一致（TRUE）扱いになり事故が起きやすい | 既定が完全一致のため誤爆しにくい |
| 検索方向の指定 | できない（常に先頭から） | 末尾から検索することも可能 |
| 複数列の同時取得 | 1列ずつしか取得できない | 戻り範囲をまとめて指定しスピル表示できる |
3-1. 検索する列が左端でなくてもよい
VLOOKUP関数では、検索したい値の列が、取得したい値の列より必ず左側になければいけません。XLOOKUP関数は検索範囲と戻り範囲を別々に指定するため、この制約がありません。「B列の商品名からA列の商品コードを取得したい」といった、VLOOKUPでは工夫が必要だった検索が、XLOOKUPならそのまま書けます。
=XLOOKUP(D1, B2:B100, A2:A100)冒頭で紹介した私の在庫管理表も、まさにこのパターンでした。商品名列を検索用に複製する作業自体が、この1本の数式で不要になります。
3-2. 列を挿入しても列番号を数え直す必要がない
VLOOKUP関数は「テーブル範囲の中で何列目か」を数字で指定する必要があり、表に新しい列を挿入すると、その数字がズレて参照先が変わってしまう事故がよく起こります。XLOOKUP関数は戻り範囲を列単位で直接指定するため、この種の事故が起きにくくなっています。
3-3. 検索方法の指定を忘れても近似一致で暴走しない
VLOOKUP関数は、検索方法の引数を省略すると自動的に近似一致（TRUE）として扱われ、意図しないデータを拾ってしまう事故につながりやすいという弱点があります。XLOOKUP関数は、既定の動作が完全一致になっているため、書き忘れによる近似一致の事故が起きにくい設計になっています。
4. 実務でよくある使用例5選
ここからは、実際の業務でよく登場するパターンを5つ紹介します。
4-1. 1つのキーから複数の情報をまとめて取得する
社員番号を1つ入力するだけで、氏名や部署など複数の情報を取得したい場合、戻り範囲を複数列にまとめて指定することで、1つの数式でスピル（複数セルへの自動展開）させて表示できます。
=XLOOKUP(F1, A2:A200, B2:D200)これまでは氏名用・部署用・内線番号用と3本の数式を並べて書いていた作業が、1本にまとまります。
4-2. 完全一致するデータがなければ近い順位のデータを返す
=XLOOKUP(G1, H2:H50, I2:I50, "該当なし", -1)第5引数に-1を指定すると、完全一致するデータがない場合に「検索値未満で最も近い値」を返します。価格帯や点数の範囲によって評価を振り分けたいときなど、VLOOKUPで近似一致（TRUE）を使っていた場面の代わりに使えます。
4-3. 検索範囲を下から探す
=XLOOKUP(J1, K2:K500, L2:L500, "該当なし", 0, -1)第6引数に-1を指定すると、検索範囲の末尾から検索を始めます。同じ商品コードが複数回登場する売上履歴のようなデータで、「一番新しい記録だけを取得したい」というときに便利です。これまでは補助列や並び替えで対応していた方も多いのではないでしょうか。
4-4. INDEX+MATCHの代わりとして使う
これまでVLOOKUPの弱点を補うために、INDEX関数とMATCH関数を組み合わせて使っていた場面も、XLOOKUP1つで書き換えられることがよくあります。
=INDEX(D1:D100, MATCH(A1, B1:B100, 0))上記のようなINDEX+MATCHの組み合わせは、次のようにXLOOKUP1つで表現できます。
=XLOOKUP(A1, B1:B100, D1:D100)2つの関数を組み合わせる必要がなくなる分、数式が短くなり、後から見直す人にとっても理解しやすくなります。INDEX関数とMATCH関数を個別に組み合わせるパターンをより詳しく知りたい方は、INDEX関数とMATCH関数の使い方でエラー別の原因早見表つきで解説していますので、あわせて参考にしてください。
4-5. スピル機能と組み合わせて複数行を一気に検索する
=XLOOKUP(F2:F10, A2:A200, B2:B200)検索値をF2:F10のように複数セルの範囲で指定すると、対応する結果が自動的に複数のセルに展開されます（スピル）。複数の商品コードから商品名を一括で調べたいときなど、これまで1件ずつ数式をコピーする必要があった作業を、まとめて一度に処理できます。
5. 乗り換えるときに注意したい5つのポイント
便利なXLOOKUPですが、使い始める前に押さえておきたい注意点もあります。
5-1. 検索範囲と戻り範囲の行数を揃える
検索範囲をA2:A100にしたのに、戻り範囲をB2:B90のように短く指定してしまうと、エラーになります。VLOOKUPよりもエラーとして気づきやすい点ではありますが、範囲を指定する際は2つの範囲の行数を必ず揃えることを意識しておく必要があります。
5-2. 「見つからない場合」の引数を省略しない
第4引数を省略すると、検索値が見つからなかった場合に「#N/A」というエラーがそのままセルに表示されます。上司や取引先に提出する資料でこの状態のまま放置すると、見た目の印象がよくありません。「該当なし」や空欄など、何を表示するかをあらかじめ決めて指定しておくことをおすすめします。
5-3. 古いバージョンのExcelでは使えない
XLOOKUP関数は、Microsoft 365やExcel 2021以降でしか使用できません。共有相手が古いバージョンのExcelを使っている場合、XLOOKUPで作った数式はエラーになってしまいます。社内で共有するファイルにXLOOKUPを使う前に、関係者が使っているExcelのバージョンを確認しておくと安心です。
5-4. VLOOKUPと混在させて表の管理が複雑にならないようにする
一部の数式はVLOOKUP、一部はXLOOKUPというように、同じ表の中で新旧の関数が混在していると、後から見直す人にとって分かりづらい表になってしまいます。新しく作る表ではXLOOKUPに統一する、既存の表は無理に書き換えないなど、切り替えの方針をあらかじめ決めておくと、表全体の一貫性を保ちやすくなります。
5-5. スピルした範囲に別のデータを入力しない
スピル機能で複数セルに結果が自動展開されている状態で、その範囲の途中のセルに別の値を直接入力してしまうと、スピルが正しく機能しなくなり「#SPILL!」というエラーが表示されます。スピルする数式の周辺には、あらかじめ他のデータを置かないようにレイアウトを工夫しておくと、このエラーを避けられます。
6. 結局、VLOOKUPとXLOOKUPどちらを使えばいい？
すでにVLOOKUPで問題なく運用できている表を、無理にXLOOKUPへ書き換える必要はありません。ただし、これから新しく作る表や、検索する列が左端に来ない構造の表では、最初からXLOOKUPを使っておく方が、将来の列挿入や検索方向の変更に強くなります。
私自身、在庫管理表をXLOOKUPに置き換えたことで、月1時間近くかかっていたコピー貼り付け作業がゼロになりました。数字にすると小さく見えるかもしれませんが、同じような表を複数抱えている職場では、この積み重ねが意外と大きな差になります。
VLOOKUPの基本的な考え方や、近似一致の危険性、重複列による事故など、検索系関数に共通する注意点については、VLOOKUP関数の基本の使い方でも詳しく取り上げていますので、基礎から確認したい方はそちらもあわせてご覧ください。
よくある質問
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は、その延長線上にある「もう一つの選択肢」として、状況に応じて使い分けていくのがちょうどいい距離感だと思います。
なお、複数条件を組み合わせた検索や、XLOOKUPが使えない古いExcelの人にファイルを渡すときの互換式など、乗り換えたあとで詰まりやすい場面はnoteにまとめています。もう一歩踏み込みたい方はXLOOKUPに乗り換えたあとで詰まる5つの場面（note）ものぞいてみてください。
コメント