XLOOKUP関数の使い方・構文とVLOOKUPとの違いを解説

Excelの表から目的のデータを取り出すとき、多くの人がまず思い浮かべるのがVLOOKUP関数ではないでしょうか。しかしVLOOKUP関数には「検索値は検索範囲の一番左の列になければならない」「表に列を挿入すると数式がずれてしまう」といった制約があり、表の並びを工夫したり、数式を頻繁に修正したりする手間に悩まされてきた方も多いはずです。

こうした制約を解消するために登場したのが、Excel 2021以降およびMicrosoft 365で利用できるXLOOKUP関数です。XLOOKUP関数はVLOOKUP関数と同じく「表引き」（表の中から条件に合う値を検索して取り出す）ための関数ですが、検索範囲と戻り範囲をそれぞれ独立して指定できるため、より直感的かつ柔軟にデータを取得できます。

本記事では、XLOOKUP関数の基本構文から、VLOOKUP関数との具体的な違い、見つからない場合の処理や検索の応用テクニックまで、実際の数式例を使いながら解説します。日々の集計作業や、複数の表を突き合わせる業務でExcelを使っている方は、ぜひ参考にしてください！

XLOOKUP関数の基本構文とVLOOKUPとの違い

XLOOKUPの基本構文と3つの必須引数

XLOOKUP関数は次の書式で入力します。
構文: =XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
最初の3つの引数（検索値・検索範囲・戻り範囲）は省略できない必須の引数です。残り3つ（見つからない場合・一致モード・検索モード）は省略可能な任意の引数で、省略した場合はExcel側の既定の動作（完全一致・先頭から検索し、見つからない場合は「#N/A」エラーを返す）が適用されます。

【必須の引数（省略不可）】
検索値：セルA2（P102）の値を検索してきて！という値です。
検索範囲：A5:A7が範囲です。検索する範囲はここです！という意味。
戻り範囲：B5:B7は↑の検索範囲（A5:A7）と対応しており、検索値が見つかった場合にここの値が取得されます。

【任意の引数（省略可）】
見つからない場合：検索値が見つからなかった場合に表示する値。省略するとエラー（#N/A）が返されます。
一致モード：完全一致ではなく、近似値も検索する場合に使用します。
検索モード：検索の方向（前方または後方）を指定したいときに使用します。

以下のような商品一覧表（5〜7行目）があるとします。

例：セルA2に検索したい商品ID「P102」を入力し、セルB2に「=XLOOKUP(A2, A5:A7, B5:B7)」と入力すると、A5:A7の中からA2と一致する行が探され、対応するB5:B7の値が取得されます。この場合、B2には「ペン」と表示されます。

特長1. 検索範囲より左側の列も検索できる（左方向検索）

VLOOKUP関数では検索値が検索範囲の一番左の列にある必要があり、たとえば商品名から商品IDを逆引きしたい場合は、あらかじめ列を並べ替えたり別表を用意したりする必要がありました。XLOOKUP関数は検索範囲と戻り範囲をそれぞれ独立して指定するため、検索値が右の列にあっても左の列にあっても関係なく検索できます。

例：セルA2に商品名「ペン」と入力し、セルB2に「=XLOOKUP(A2, B5:B7, A5:A7)」と入力すると、商品名の列（B5:B7）からIDの列（A5:A7）を逆方向に検索し、B2には「P102」と表示されます。

特長2. 列の増減があっても数式を修正しなくてよい

VLOOKUP関数は「戻り範囲の何列目を取得するか」を列番号（例：3列目なら「3」）で指定する仕組みのため、表の途中に新しい列を挿入すると列番号がずれてしまい、数式を都度修正する必要があります。

たとえば単価を取得するために「=VLOOKUP(A2, A5:D7, 3, FALSE)」と入力していた場合、C列とD列の間に新しい列を挿入すると、単価ではなく別の値が返ってしまうことがあります。一方XLOOKUP関数は戻り範囲そのものをセル範囲（例：D5:D7）で直接指定するため、離れた場所に列を挿入しても参照範囲がずれず、数式を修正せずに済みます。

特長3. 複数列のデータを一度に取得できる（スピル機能）

XLOOKUP関数は、戻り範囲に複数列を指定すると、1つの数式で複数のセルに結果を展開する「スピル」機能に対応しています。

例：セルB2に「=XLOOKUP(A2, A5:A7, B5:D7)」と入力すると、A2の「P102」に対応する行のB列～D列の値がまとめて取得され、B2に「ペン」、C2に「M」、隣のD2に「120」が自動的に表示されます。このときD2セルには数式を入力する必要はありません。

特長4. 見つからない場合の処理をIFERROR関数なしで指定できる

VLOOKUP関数で「見つからない場合に別の文字列を表示したい」ときは、IFERROR関数と組み合わせて次のように入力する必要がありました。

例：=IFERROR(VLOOKUP(A2, A5:B7, 2, FALSE), "商品なし")

XLOOKUP関数では4番目の引数「見つからない場合」に直接指定できるため、IFERROR関数を別途組み合わせる必要がありません。

例：セルA2に一覧にない商品ID「P999」を入力し、セルB2に「=XLOOKUP(A2, A5:A7, B5:B7, "商品なし")」と入力すると、B2には「商品なし」と表示されます。

なお、この4番目の引数を省略した場合は、これまで通り「#N/A」エラーが返されます。

任意引数「一致モード」で曖昧な検索に対応する

5番目の引数「一致モード」では、検索値の一致のさせ方を指定できます。指定できる値は、完全一致を表す「0」（省略時の既定値）、検索値以下で最も近い値を返す「-1」、検索値以上で最も近い値を返す「1」、そしてアスタリスク（*）やクエスチョン（?）を使ったワイルドカード検索を行う「2」の4種類です。

例：商品名の一部しか分からない場合、セルA2に「=XLOOKUP("ペ*",B5:B7,A5:A7,"該当なし",2)」と入力すると、「ペ」で始まる商品名（この場合は「ペン」）が検索され、A2には「P102」と表示されます。

一致モードを省略、またはセルA2に「=XLOOKUP("ペ*",B5:B7,A5:A7,"該当なし",0)」を指定した場合は完全一致でしか検索されないため、「ペ*」がそのままの文字列として扱われて該当する商品が見つからず、「該当なし」と表示される点に注意してください。

任意引数「検索モード」で検索の向きを指定する

6番目の引数「検索モード」では、検索範囲のどちら側から検索を始めるかを指定できます。既定値の「1」は先頭（上）から検索し、「-1」は末尾（下）から検索します。また、表があらかじめソートされている場合に限り、高速な二分探索を行う「2」（昇順）・「-2」（降順）も指定できます。

たとえば同じ商品IDの入出庫記録が複数行にわたって並んでいる台帳があり、最新の記録だけを取得したいとします。検索モードに既定値の「1」を使った場合は上（古い方）から検索されるため最も古い記録が返ってきますが、検索モードを「-1」に変更すると下（最新の方）から検索されるようになり、直近の記録を取得できます。

例：出庫記録が4〜14行目に並んでいる表で、セルA2に「=XLOOKUP("P102", A4:A14, B4:B14, "記録なし", 0, -1)」と入力すると、A4:A14の中を下から順に「P102」を探し、最初に一致した行（＝最も下にある、最新の記録）のB列の値がA2に表示されます。

VLOOKUP関数とXLOOKUP関数の違い一覧

現場で頻発する限界とリスク

ここまで見てきた通り、XLOOKUP関数はVLOOKUP関数の弱点を数多く解消した便利な関数です。一方で、Excelの数式だけで業務のデータ管理を続ける場合、XLOOKUP関数を使いこなせたとしても避けられない限界がいくつかあります。

限界1. 古いExcelバージョンとの互換性がない

XLOOKUP関数はExcel 2021以降およびMicrosoft 365でのみ使用できる関数です。取引先や社内の一部メンバーがExcel 2019以前のバージョンやOffice互換ソフトを使っている場合、ファイルを開いた瞬間にXLOOKUP関数を使ったセルが「#NAME?」エラーとして表示されてしまい、そもそも数式自体が動作しません。共有相手の環境がすべて最新バージョンに揃っているとは限らない業務では、VLOOKUP関数など互換性の高い関数と使い分ける必要が出てきます。

限界2. 複数ファイル・複数人でのデータ集計には限界がある

XLOOKUP関数は、あくまで1つのブック内、もしくは開いている別ブックを参照する仕組みであり、部署やプロジェクトごとにファイルが分かれている状態でリアルタイムに全体のデータを突き合わせることには向いていません。参照するファイルが増えるほど数式が複雑になり、ファイル名の変更や保存場所の移動によってリンク切れが起こりやすくなるほか、複数人が同時に同じファイルを編集すると上書き保存によるデータ消失のリスクも高まります。

限界3. 数式が複雑化し、属人化するリスクがある

一致モードや検索モードまで使いこなした数式は非常に強力ですが、その分、数式の意味を正しく理解できるメンバーが限られてしまいがちです。数式を組んだ本人が異動・退職した後、残されたメンバーが引数の意味を理解できず、数式を修正できないまま放置されるケースは現場でよく見られます。結果として、Excelファイルは「触ってはいけないブラックボックス」と化し、業務の属人化がさらに進んでしまいます。

まとめ

本記事では、XLOOKUP関数の基本構文である「検索値」「検索範囲」「戻り範囲」の3つの必須引数から、「見つからない場合」「一致モード」「検索モード」という3つの任意引数の使い方まで、具体的な数式例とともに解説しました。VLOOKUP関数と比べて、左方向検索や複数列の同時取得ができ、IFERROR関数を組み合わせなくてもエラー処理を指定できる点が、XLOOKUP関数の大きなメリットです。

一方で、対応バージョンの制約や、複数人・複数ファイルでのデータ集計、数式の属人化といった課題は、関数の使い方の工夫だけでは解決できません。日々の業務でExcelの限界を感じ始めているようであれば、関数の使い方にとどまらない、業務の標準化そのものを見直すタイミングかもしれません。

現場のスキル底上げ・業務標準化を実現：実務で使える「OBPM Neoの研修サービス」 OBPM Neoでは、プロジェクト管理の標準化や現場で役立つ実践スキルを体系的に学べる研修プログラムを提供しています。 \すぐに実務で活かせる「使える」研修としてPM経験者が伴走！/ OBPM Neoの「PM経験者が指導する研修サービス」の詳細はこちら