【Excel】VLOOKUPとXLOOKUPの違い|完全一致・列追加・互換性の確認

【Excel】VLOOKUPとXLOOKUPの違い|完全一致・列追加・互換性の確認
🛡️ 超解決

VLOOKUPからXLOOKUPへ変更すると、検索する列と取り出す列を直接指定でき、数式を読みやすくできます。ただし、VLOOKUPを使っていること自体がセキュリティ上の欠陥になるわけではありません。移行するかどうかは、完全一致の指定、表の変更頻度、共有相手のExcelのバージョンで判断します。

商品コードから価格を求める例で、両関数の違いと、置き換える前に確認したい点を整理します。正常に動いている共有ブックを、互換性を確認せず一括変更する必要はありません。

ADVERTISEMENT

1. VLOOKUPで確認したい検索方法と列番号

VLOOKUPは、指定範囲の左端の列から検索し、同じ行の指定した列の値を返します。次の例ではA列が商品コード、B列が商品名、C列が価格です。E2に「P02」を入力して価格を調べます。

行 A:商品コード B:商品名 C:価格
2 P01 ノート 180
3 P02 ペン 120
4 P03 ファイル 250
=VLOOKUP(E2,$A$2:$C$4,3,FALSE)

この数式は120を返します。末尾のFALSEが完全一致、3が範囲内の左から3列目を表します。範囲を絶対参照にしているため、数式を下の行へコピーしても検索表は動きません。

商品コードP02をA3で探し、同じ行のC3の価格120を返す。VLOOKUPは範囲の3列目、XLOOKUPは価格列を直接指定する。
P02の価格は120。取り出す列を数える指定と、価格列を直接指定する方法を比較します。

完全一致を省略しない

商品コードや社員番号の検索では、通常はFALSEを指定します。省略した場合は近似一致になり、左端の列を昇順に並べた表が前提です。近似一致は料金区分や点数の区分を求める用途には有用ですが、存在しない商品コードを検出する目的とは異なります。仕様はMicrosoftのVLOOKUP解説で確認できます。

列を追加したら、取り出す列を再確認する

例のB列とC列の間に新しい列を挿入すると、元の価格はD列へ移動します。検索範囲が広がっても、第3引数の数値3は自動的に4にはなりません。エラーにならず別の項目を返す場合があるため、表を編集した後は結果の意味まで確認します。列番号はMATCHで見出しから求める方法もあり、必ず数値を直接入力する必要があるわけではありません。

※ お探しの解決策が見つからない場合は、こちらの「Excelトラブル完全解決データベース」で他のエラー原因や解決策をチェックしてみてください。

2. XLOOKUPで検索列と価格列を直接指定する

同じ表では、次の数式に置き換えられます。A2:A4で商品コードを探し、対応するC2:C4の価格を返します。

=XLOOKUP(E2,$A$2:$A$4,$C$2:$C$4,"該当なし")

XLOOKUPの既定は完全一致なので、E2がP02なら120、存在しないP99なら「該当なし」です。検索列と価格列の間へ列を挿入しても、通常は価格列への参照が移動に合わせて調整されます。戻り範囲を検索範囲の左側に置くこともできます。

ただし、参照する列そのものを削除した場合や、検索列と価格列で行の対応を崩した場合まで防げるわけではありません。また、同じコードが複数あると、既定では先に見つかった行を返します。移行前にコードの重複と範囲の始点・終点を点検してください。

「該当なし」の指定は、検索値が見つからない場合の処理です。参照先の削除や範囲の不整合など、すべてのエラーを処理する引数ではありません。詳しい引数と検索方向はMicrosoftのXLOOKUP解説に掲載されています。

3. 置き換える前に互換性と利用目的を比較する

確認点 VLOOKUP XLOOKUP
検索方法の省略 近似一致。コード検索はFALSEを明示 完全一致
取り出す場所 指定範囲内の列番号で指定 戻り範囲を直接指定
左側の値の取得 通常の範囲指定では不可 可能
見つからない場合 #N/A。必要ならIFNAなどで処理 第4引数で返す値を指定できる
Excel 2016・2019との共有 利用できる 利用できない

XLOOKUPはMicrosoft 365、Excel 2021・2024などで使えますが、Excel 2016・2019では使えません。社外へ渡すファイルは、受け取り側が編集・再計算できるかまで確認します。互換性が必要なら、VLOOKUPの完全一致を維持する、またはINDEXとMATCHを組み合わせる選択肢があります。

4. 一部の数式で結果を照合してから移行する

  1. ブックを別名で保存し、元の数式を残します。
  2. 補助列へXLOOKUPの式を入れ、既存の結果と比較します。
  3. 存在するコード、存在しないコード、重複するコード、空欄をそれぞれ試します。
  4. 表への列追加と、共有先での再計算を確認してから置き換えます。

数式が短くなっても、検索元のデータが正しくなるわけではありません。コードと価格の対応、重複の扱い、更新後の照合まで決めておくことが、誤った集計を防ぐうえで重要です。

📊
Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。
この記事の監修者
📈

超解決 Excel・Word研究班

企業のDX支援や業務効率化を専門とする技術者チーム。20年以上のExcel・Word運用改善実績に基づき、不具合の根本原因と最短の解決策を監修しています。ExcelとWordを使った「やりたいこと」「困っていること」「より便利な使い方」をクライアントの視点で丁寧に提供します。

🏆
超解決 Excel検定 あなたのExcel実務能力を3分で測定!【1級・2級・3級】