XLOOKUP と VLOOKUP の違い|構文の比較と、置き換えなくてよい場面

結論

XLOOKUP は、表から値を探して対応する値を返す検索関数で、VLOOKUP の弱点をいくつか解消した後継的な位置づけの関数です。Microsoft のサポートでも、VLOOKUP のページで「改良版であり、任意の方向で機能し、既定で完全一致を返す」関数として XLOOKUP が案内されています。

主な違いは次の3点です。

  • 既定の一致方法:XLOOKUP は完全一致が既定。VLOOKUP は省略すると近似一致になる
  • 範囲の指定方法:XLOOKUP は「検索範囲」と「戻り範囲」を別々に指定する。VLOOKUP は1つの表範囲と、その中の列番号で指定する
  • 探せる方向:XLOOKUP は検索列の左側にある列の値も返せる。VLOOKUP は範囲の左端の列で検索し、右側の列しか返せない

ただし、XLOOKUP は Excel 2016 および Excel 2019 では使用できないことが公式に明記されています。使える環境かどうかが、使い分けの前提になります。

セナ
セナ新しめの Excel なら XLOOKUP でだいたい足りる

意味・仕組み

そもそも何をする関数か

どちらも「一覧表の中から、指定した値と同じ行を探し、その行にある別の値を取り出す」関数です。たとえば商品コードを手がかりに、商品マスターから商品名や単価を取り出す、といった使い方をします。

違いは、その指定の仕方と考え方にあります。

VLOOKUP は、表全体を1つの範囲として渡し、「左から何列目の値を返すか」を番号で指定します。検索する値は、その範囲の最初の列に無ければならず、返せるのはそれより右の列の値だけです。

XLOOKUP は、検索する列(検索範囲)と、値を取り出す列(戻り配列)を、それぞれ独立した範囲として渡します。2つの範囲は同じ行数で対応していればよく、戻り配列が検索範囲の左側にあっても構いません。また、戻り配列に複数列を指定すると、複数の値をまとめて返せます。

検索の向きと順序

VLOOKUP は範囲の上から下へ探すのが基本です。XLOOKUP は検索モードを指定でき、先頭から探す(既定)のほか、末尾から逆向きに探すこともできます。同じ検索値が複数行ある表で「最後の1件」を取りたい、といった場面ではこの違いが効きます。

使い方

構文の比較

それぞれの構文は次のとおりです。

=VLOOKUP(検索値, 範囲, 列番号, [検索の型])
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])

引数を並べて比べると、考え方の違いが見えてきます。

項目VLOOKUPXLOOKUP
検索値第1引数第1引数(同じ)
範囲の指定表全体を1つの範囲で指定し、検索値はその最初の列に必要検索範囲と戻り配列を別々に指定する
戻す値の指定範囲の中での列番号(左端が1)戻り配列そのものを範囲で指定する
既定の一致方法近似一致(第4引数を省略すると TRUE)完全一致(一致モード 0 が既定)
検索方向上から下のみ(左端列を検索)戻り配列は検索範囲の左右どちらでも可。検索モードで末尾からの検索も可
見つからない場合#N/A エラーになる。表示を変えるには IFERROR 等で包む第4引数「見つからない場合」で代わりの値を直接指定できる。省略時は #N/A

同じ検索を両方で書く

商品コード(セル F2)から、表 B2:D11 の3列目にある単価を取り出す例です。

VLOOKUP では、表全体と列番号で書きます。

=VLOOKUP(F2,B2:D11,3,FALSE)

XLOOKUP では、検索列 B2:B11 と戻す列 D2:D11 を分けて書きます。

=XLOOKUP(F2,B2:B11,D2:D11)

XLOOKUP 側で完全一致の指定が見当たらないのは、完全一致が既定だからです。VLOOKUP で完全一致にするための FALSE の書き忘れは、近似一致で意図しない値が返る事故のもとでした。その心配が減るのは実務上の大きな違いです。

見つからない場合の表示まで含めるなら、XLOOKUP では第4引数に書けます。

=XLOOKUP(F2,B2:B11,D2:D11,"該当なし")

VLOOKUP で同じことをするには、IFERROR で全体を包む形になります。

=IFERROR(VLOOKUP(F2,B2:D11,3,FALSE),"該当なし")

XLOOKUP の利点

  • 左側の列も取れる:検索範囲と戻り配列が独立しているため、表の並びを入れ替えなくても、検索列より左にある値を返せます
  • 完全一致が既定:第4引数の書き忘れによる近似一致の事故が起きにくくなります
  • 列挿入で壊れにくい:VLOOKUP の列番号指定は、表の途中に列を挿入すると指す列がずれてしまいます。XLOOKUP は戻り配列を範囲で指定するため、列番号のずれという壊れ方がありません
  • 見つからない場合を数式内で指定できる:IFERROR で包まずに済み、数式が短くなります。また、IFERROR と違い「見つからなかった」以外のエラーを隠しません

使える環境の注意

XLOOKUP は新しい関数であり、Microsoft のサポートには Excel 2016 および Excel 2019 では使用できないと明記されています。手元の環境が Microsoft 365 や Excel 2021 以降かどうかを確認してから使ってください。

古いバージョンの Excel で XLOOKUP 入りのブックを開くと、関数として認識されず、意図した結果が得られないことがあります。取引先や他部署など、開く側の環境が混在するファイルでは、この点が置き換えの判断材料になります。

既存の VLOOKUP を無理に置き換えなくてよい場面

  • すでに正しく動いている VLOOKUP の数式が大量にあるブック。動いているものを一斉に書き換える作業自体が、ミスの混入経路になります
  • Excel 2016 / 2019 を使う人が開く可能性があるファイル。互換性を優先するなら VLOOKUP のままが無難です
  • 表の構造が単純で、列挿入の予定もなく、完全一致の FALSE も正しく書かれている場合。得られる改善が小さいなら、触らない判断も妥当です

新規に数式を書く場面で、手元も共有先も新しい Excel でそろっているなら、XLOOKUP を選ぶ理由は十分にあります。

つまずき所・よくある失敗

  • VLOOKUP で第4引数を省略してしまう:省略時は近似一致になり、検索値と違う行の値が返ることがあります。完全一致にしたい場合は必ず FALSE(または 0)を指定してください。XLOOKUP にはこの落とし穴がありません
  • XLOOKUP の一致モードを安易に変える:一致モードの -1 や 1 は「見つからない場合に次に小さい/大きい項目を返す」近似の指定です。料金表の区分判定など意図がある場合以外は、既定の 0(完全一致)のまま使うのが基本です
  • 検索モードのバイナリ検索(2 / -2)を並べ替えなしで使う:バイナリ検索は検索範囲が昇順/降順に並べ替えられている前提のモードです。前提を満たさないと無効な結果が返ります。通常は既定の 1 で問題ありません
  • 列番号の数え間違い(VLOOKUP):列番号は表範囲の左端を1として数えます。シートの列番号(A=1…)とは別物です。範囲の開始列がずれると、戻る値もずれます
  • 環境違いで開いてしまう:前述のとおり、古い Excel では XLOOKUP が使えません。「自分の PC では動いた」で共有してしまうと、相手側で初めて問題に気づく形になります

まとめ

  • XLOOKUP は検索範囲と戻り配列を分けて指定し、完全一致が既定、左側の列や末尾からの検索にも対応する
  • VLOOKUP は表範囲+列番号の指定で、完全一致には FALSE の明示が必要。省略すると近似一致になる
  • XLOOKUP は列挿入で壊れにくく、見つからない場合の値も数式内で指定できる
  • ただし Excel 2016 / 2019 では XLOOKUP は使えない。共有先の環境が混在する場合は VLOOKUP のままにする判断も正しい

関連記事

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です