Excelの検索関数はXLOOKUPとVLOOKUPどっち?左側の列・完全一致と古いExcelの互換性

事務職・OA・業務改善

Excelで社員番号から部署名を調べたり、商品コードから単価を表示したりするときに使うのが検索関数です。

以前からあるVLOOKUPと新しいXLOOKUPのどちらを使えばいいのか迷うこともあるでしょう。

新しい台帳を作るならXLOOKUPの方が使いやすい場面は多くあります。ただし取引先や社内の別部署へファイルを渡すなら、使う人のExcelのバージョンを無視できません。

結論は「利用者全員が対応版ならXLOOKUP」「Excel 2016・2019で数式を使う人がいるならVLOOKUPやINDEX+MATCHを検討」です。

この記事では同じ社員名簿を使って数式の違いを比較します。左側の列を検索する方法、完全一致、列の追加、古いExcelとの互換性まで確認しましょう。

XLOOKUPとVLOOKUPの違いは?まず比較表で確認

比較項目 XLOOKUP VLOOKUP
検索する列 独立した検索範囲を指定 指定範囲の左端列
左側の列を返す できる 通常の構文ではできない
完全一致 既定で完全一致 FALSEまたは0を指定する
見つからない場合 第4引数で「該当なし」などを指定可能 IFNAなどと組み合わせる
戻り列の指定 セル範囲で直接指定 表の左端から数えた列番号
Excel 2016・2019 利用不可 利用可能
Microsoft 365・Excel 2021・2024 利用可能 利用可能

Microsoft公式はXLOOKUPをVLOOKUPの改良版として紹介しています。左右どちらの方向にも検索でき、完全一致が既定である点が特徴です。

一方でXLOOKUPはExcel 2016・2019には搭載されていません。ファイルを開く人の環境を確認して選ぶ必要があります。

参照:Microsoft公式「XLOOKUP関数」・Microsoft公式「VLOOKUP関数」

同じ社員名簿を使ってXLOOKUPとVLOOKUPを比較してみる

架空の社員名簿を例にします。A列に名前、B列に社員番号、C列に部署、D列に月額手当が入っている想定です。

行 A列:氏名 B列:社員番号 C列:部署 D列:手当
1 氏名 社員番号 部署 手当
2 佐藤 00101 営業 10,000円
3 田中 00102 経理 8,000円
4 鈴木 00103 総務 9,000円
5 高橋 00104 営業 10,000円

セルG2に社員番号「00102」を入力し、H2に部署名「経理」を表示することにします。

社員番号は先頭の0を保持するため、B列とG2を文字列として入力している前提です。数字の101や102として保存すると別の値になり、結果が一致しない場合があります。

XLOOKUPなら検索する列と返す列を直接指定できる

H2へ次の式を入れます。

=XLOOKUP(G2,$B$2:$B$5,$C$2:$C$5,"該当なし")

意味は次のとおりです。

  • G2:探す社員番号
  • $B$2:$B$5:社員番号を検索する範囲
  • $C$2:$C$5:見つかった行から部署名を返す範囲
  • "該当なし":一致する番号がなかった場合に表示する文字

G2に「00102」が入っていればH2には「経理」が表示されます。

XLOOKUPは既定で完全一致を使います。社員番号のように正確な一致が必要な場合は、通常の数式で一致モードを省略できます。

VLOOKUPなら検索範囲の左端列と列番号を指定する

同じ結果をVLOOKUPで求める式はこちらです。

=VLOOKUP(G2,$B$2:$D$5,2,FALSE)

$B$2:$D$5 の左端はB列です。そこに社員番号があるため検索できます。

戻す部署はその範囲の2列目にあるC列なので、列番号に 2 を指定します。

最後の FALSE は完全一致の指定です。

この式も「00102」を検索すると「経理」を返します。

社員番号が見つからなかった場合も「該当なし」と表示したいなら、次のようにIFNAを組み合わせます。

=IFNA(VLOOKUP(G2,$B$2:$D$5,2,FALSE),"該当なし")

検索値が存在しないときの #N/A を別の文字へ置き換えられます。ただし本来調べるべき入力ミスまで見逃さないよう、台帳の運用ルールも決めてください。

左側の列を検索したいならXLOOKUPが便利

先ほどの表ではB列に社員番号があり、A列に氏名があります。

G2の社員番号「00102」から左側のA列にある「田中」を返したい場合を考えましょう。

XLOOKUPなら次の式で対応できます。

=XLOOKUP(G2,$B$2:$B$5,$A$2:$A$5,"該当なし")

検索列はB列、戻す列はA列です。戻す列が検索列より左にあっても問題ありません。

一方でVLOOKUPは指定した範囲の左端列で検索し、そこから右方向の列番号を指定する関数です。

この表のままB列の社員番号からA列の氏名を返す処理は、一般的なVLOOKUPの式では実現できません。

VLOOKUPを使い続けるなら列を並べ替える方法があります。ただし既存のシステムから出力した台帳を勝手に並べ替えると他の集計に影響する場合があります。

表を並べ替えずに左側の値を返したいならXLOOKUPが分かりやすい選択です。

Excel 2016・2019で左側の値を返すならINDEX+MATCH

配布先が古いExcelの場合、XLOOKUPを使えないことがあります。

その場合はINDEX関数とMATCH関数の組み合わせが選択肢になります。

先ほどと同じ表で、G2の社員番号からA列の氏名を返す式はこちらです。

=INDEX($A$2:$A$5,MATCH(G2,$B$2:$B$5,0))

MATCHはB列でG2と一致する社員番号が何番目にあるかを調べます。末尾の 0 は完全一致です。

INDEXはその位置に対応するA列の氏名を返します。

一致する社員番号がないときに文字を表示するなら次のように書けます。

=IFNA(INDEX($A$2:$A$5,MATCH(G2,$B$2:$B$5,0)),"該当なし")

この方法ならExcel 2016・2019でも左右の列配置に縛られにくくなります。

ただしXLOOKUPに比べると式が長くなるため、ほかの担当者が保守できるかも考えて選びましょう。

参照:Microsoft公式「VLOOKUP、INDEX、またはMATCHを使って値を検索する」

完全一致ならVLOOKUPはFALSE、XLOOKUPは既定のまま

社員番号や商品コードを検索するときは、誤った行を拾わないことが重要です。

XLOOKUPは一致モードを省略すると完全一致で検索します。

対してVLOOKUPは第4引数の 検索の型 を省略すると近似一致になります。完全一致にしたいときは必ず FALSE または 0 を指定しましょう。

方法 式の例 検索方法
XLOOKUP =XLOOKUP(G2,B2:B5,C2:C5) 完全一致(既定)
VLOOKUP =VLOOKUP(G2,B2:D5,2,FALSE) 完全一致
VLOOKUP =VLOOKUP(G2,B2:D5,2) 近似一致(省略時)

近似一致は条件に合う最大値を探すなど特定の用途では役立ちます。しかし社員番号や商品コードで使うと意図しない結果が返る可能性があります。

業務台帳ではまず完全一致を基本にしてください。

列を挿入したときに壊れやすいのはどっち?

VLOOKUPは検索範囲の左端から何列目を返すかを数字で指定します。

例えば =VLOOKUP(G2,$B$2:$D$5,2,FALSE) はB列を検索して2列目のC列を返します。

ここでB列とC列の間に新しい列を挿入し、VLOOKUPの列番号を変更しなければ、新しい2列目を返してしまう場合があります。

つまり「数字の2が何の列を意味しているか」は表の構造に依存します。

一方でXLOOKUPは検索列と戻す列を直接指定します。

=XLOOKUP(G2,$B$2:$B$5,$C$2:$C$5,"該当なし")

元のC列が右へ移動した場合でもExcelがセル参照を追従させるため、変更に強い場合があります。

ただしXLOOKUPなら列の挿入・削除をしても絶対に壊れないわけではありません。

参照した列そのものを削除した場合や、別の範囲をコピーして構造を変えた場合などは式の確認が必要です。

列を変更した後は代表的な社員番号を使って、正しい氏名・部署・手当が返るか必ず検算しましょう。

Excel 2016・2019の人へ配るファイルなら互換性を最優先に

Microsoft公式ではXLOOKUPがExcel 2016とExcel 2019で利用できないと明記されています。

Microsoft 365やExcel 2021・2024などの対応版で作ったXLOOKUP入りのブックを古いExcelで開いた場合、数式を正しく計算できない可能性があります。

バージョンが異なる人と同じファイルを編集するなら、次のように考えます。

配布・共同作業の状況 おすすめの方法
全員がMicrosoft 365・Excel 2021・2024などの対応版 XLOOKUPを優先して検討
Excel 2016・2019利用者も数式を編集する VLOOKUPまたはINDEX+MATCH
検索した結果だけを納品する 計算済みの値へ変換して渡す方法も検討
利用者のExcel版が分からない 受け渡し前に確認し、旧版でも動く方式を選ぶ

特に「取引先がファイルを開いて編集する」「古いパソコンのExcelで毎月集計する」という場合は注意が必要です。

自分のパソコンでXLOOKUPが動くという理由だけで選ばず、配布先の環境まで確認してください。

値だけに変換して渡す場合は数式がなくなり、検索値を変えても結果が自動更新されません。編集用の原本は別に保存しておきましょう。

数式を切り替える前に確認したい3つの落とし穴

1. 社員番号の先頭の0が消えていないか

先ほどの「00102」のように先頭に0が付いた番号は、Excelで通常の数値として入力すると「102」に変わる場合があります。

このときマスター側のB列が文字列「00102」、検索用のG2が数値102では一致しないことがあります。

社員番号や商品コードは計算するための数字ではなく識別子です。先頭0を使う場合は列と検索値の両方を文字列で統一して入力するのが基本です。

セルの見た目だけを「00000」の表示形式にしている場合は実際の保存値を確認してください。

2. 同じ社員番号が2行あると最初に見つかった行が返る

XLOOKUPの既定の検索方向は先頭からです。VLOOKUPも通常は上から探します。

同じ社員番号が複数行に存在する場合、期待している方とは違う行の値を返すおそれがあります。

社員番号や商品コードが一意である前提なら、重複がないか先に確認しましょう。

日付ごとに同じコードが何行もある表なら、コードに加えて年月などを条件にする設計が必要です。

3. 数字と文字列が混在していないか

セルに「102」と表示されていても、数値の102と文字列の「102」は同じとは限りません。

インポートしたCSVや外部システムのデータでは、見た目が同じでも型が違うことがあります。

#N/A が表示されたら式を書き換える前に、検索値と元データの型や余分な空白を確認しましょう。

単にIFNAでエラーを隠してもマスターの不整合は解消しません。

XLOOKUPなら必ずVLOOKUPから置き換えた方がいい?

すでに動いている業務ブックをすべてXLOOKUPへ書き換える必要はありません。

たとえば検索キーが表の左端にあり、部署名や単価を右側から返すだけならVLOOKUPでも目的を満たせます。

古いExcelへ配布している場合は、既存のVLOOKUPを維持する方が安全なこともあります。

一方で新しい表を作り、利用者全員が対応版を使っているならXLOOKUPの方が列の配置に柔軟で、式を読み直すときも分かりやすくなります。

「新しい関数だから全面変更」ではなく、使う人の環境と表の構造に合わせて選ぶのがよいでしょう。

よくある質問

Excel 2019でXLOOKUPを使えますか?

使えません。Microsoft公式はExcel 2016とExcel 2019にXLOOKUPがないと案内しています。VLOOKUPやINDEX+MATCHなどの関数を使ってください。

XLOOKUPは検索列の左側にある値も取得できますか?

できます。検索範囲と戻り範囲を個別に指定できるので、検索列より左にある氏名や価格なども取得できます。

VLOOKUPでFALSEを書かないとどうなりますか?

第4引数を省略すると近似一致になります。社員番号のような識別子では意図しない結果を返す可能性があるため、完全一致のFALSEを指定しましょう。

新しいExcelで作成したXLOOKUP入りのファイルを古いExcelで開くと?

古いExcelではXLOOKUPを再計算できません。表示や再計算の結果が期待どおりにならない可能性があります。値として渡すか対応する関数に変更してから配布する必要があります。

VLOOKUPとXLOOKUPはどちらが必ず速いですか?

関数名だけで一律に速いとは判断できません。データ件数、検索方法、参照範囲、ブック内のほかの計算にも左右されます。大量のデータを扱う場合は実際のブックで計測してください。

まとめ|新規の業務台帳ならXLOOKUP、旧Excelへの配布ならVLOOKUPも選択肢

XLOOKUPは検索する列と返す列を分けて指定でき、左右どちらの方向にも検索できます。完全一致が既定なので社員番号や商品コードの検索にも使いやすい関数です。

VLOOKUPは検索範囲の左端列から右へ検索するため、左側の列を返す用途には向きません。また完全一致にはFALSEを指定します。

ただしXLOOKUPはExcel 2016・2019で利用できません。

新しい台帳を作るならまず利用者のExcelバージョンを確認し、対応版のみならXLOOKUPを検討しましょう。古いExcelも使うならVLOOKUPやINDEX+MATCH、値だけで納品する方法を選びます。

どちらの関数でも先頭0、重複キー、数値と文字列の違いを検算することが大切です。

公式仕様はMicrosoftのXLOOKUP関数ガイドとVLOOKUP関数ガイドで確認できます。