VLOOKUPとXLOOKUPの違いと使い方｜どちらを使うべきか徹底比較
しーとちゃん
けんじさん、VLOOKUPは何とか使えるようになったんですけど、最近XLOOKUPっていう関数も聞くようになって、違いがよくわからないんです。
けんじ
いい質問だね。実はXLOOKUPは、VLOOKUPの弱点を解消するために後から追加された関数なんだ。
しーとちゃん
じゃあ、これからはXLOOKUPだけ覚えればいいんですか？
けんじ
実はそう単純でもなくて。バージョンの制約もあるから、両方の特徴を知っておくのが一番なんだ。今日はその違いを詳しく説明するね。
前回の記事まではショートカットキーを紹介してきましたが、今回は検索系の代表的な関数、VLOOKUPとXLOOKUPを取り上げます。どちらも「表の中から特定のデータを探し出す」という点では同じですが、できることと制約に大きな違いがあります。
自分が事務職の仕事で最も多用してきたのがこの検索系の関数です。この記事では、それぞれの使い方と、実務でどちらを選ぶべきか、そして初心者がほぼ必ずぶつかる #N/A エラーの原因までまとめて整理します。
VLOOKUPの基本
VLOOKUPは「Vertical Lookup（垂直方向の検索）」の略で、指定した値を表の一番左の列から探し、該当する行の別の列の値を返す関数です。
基本の書き方
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
| 引数 | 内容 |
|---|---|
| 検索値 | 探したい値（社員番号や商品コードなど） |
| 範囲 | 検索対象の表全体 |
| 列番号 | 範囲の中で、左から何列目の値を返すか（1から数える） |
| 検索方法 | FALSE＝完全一致、TRUEまたは省略＝近似一致 |
たとえば、A列に社員番号、B列に氏名が入った表から、社員番号「1001」の氏名をD1セルに探したい場合は、以下のように書きます。
=VLOOKUP(1001, A:B, 2, FALSE)
VLOOKUPを使う際の注意点
自分が新人の頃に一番ハマった落とし穴は、「検索方法」の指定を省略してしまうことでした。検索方法を省略すると自動的に近似一致（TRUE）として扱われ、意図しない値が返ってくることがあります。基本的には常に FALSE（完全一致）を指定する、と覚えておくのが安全です。
もうひとつの重要な制約は、VLOOKUPは検索値が「範囲の一番左の列」にある必要があるという点です。つまり、探したい値より左側にある列のデータは取得できません。
範囲は必ず固定する（絶対参照）
数式を1つ書いて下方向にオートフィルすると、範囲もいっしょにずれていきます。1行目で A2:B100 と書いた数式を10行目までコピーすると、10行目では A11:B109 を見にいってしまい、表の上のほうにあるデータが検索対象から外れます。「上のほうの行だけエラーにならないのに、下のほうがおかしい」という症状は、たいていこれが原因です。
対策は、範囲を絶対参照にすることです。範囲を選択した状態で F4 キーを押すと $A$2:$B$100 のように $ がつき、コピーしてもずれなくなります。
=VLOOKUP($D2, $A$2:$B$100, 2, FALSE)
検索値の側は、右方向にコピーする可能性があるなら列だけ固定（$D2）しておくと扱いやすくなります。列全体を A:B のように指定する書き方でもずれは防げますが、データ量が多いと計算が重くなるため、実務では範囲を絞って絶対参照にするほうが無難です。
列番号を手で数えない
VLOOKUP(..., 5, FALSE) のように列番号を直接書くと、あとから表の途中に列を1本挿入しただけで、参照先が1つずれて別の項目が返ってきます。しかもエラーにならず「それっぽい値」が返るので、気づきにくいのが厄介なところです。
これを避けるには、列番号をMATCH関数で求めます。
=VLOOKUP($D2, $A$2:$H$100, MATCH("部署", $A$1:$H$1, 0), FALSE)
MATCHが見出し行から「部署」の位置を毎回探してくれるので、列を挿入しても正しい列を返し続けます。ひと手間かかりますが、他の人が触るファイルではこの形にしておくと事故が減ります。
XLOOKUPの基本
XLOOKUPは、Microsoft 365およびExcel 2021以降で使える、比較的新しい検索関数です。VLOOKUPの弱点を解消する形で設計されています。
基本の書き方
=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合)
| 引数 | 内容 |
|---|---|
| 検索値 | 探したい値 |
| 検索範囲 | 検索値を探す列（または行） |
| 戻り範囲 | 結果として返したい値がある列（または行） |
| 見つからない場合 | 該当データがない場合に表示する値（省略可） |
先ほどと同じ例をXLOOKUPで書くと、以下のようになります。
=XLOOKUP(1001, A:A, B:B, "該当なし")
第5引数・第6引数まで使うとできること
XLOOKUPには、省略しがちな第5引数（一致モード）と第6引数（検索モード）があります。ここまで使えると、VLOOKUPでは面倒だった処理が一気に短く書けます。
| 引数 | 指定値 | 動作 |
|---|---|---|
| 一致モード（第5） | 0 | 完全一致（省略時のデフォルト） |
| 一致モード（第5） | 1 | 完全一致、なければ検索値より大きい次の値 |
| 一致モード（第5） | -1 | 完全一致、なければ検索値より小さい次の値 |
| 一致モード（第5） | 2 | ワイルドカード（* や ?）で一致 |
| 検索モード（第6） | 1 | 先頭から末尾へ検索（省略時のデフォルト） |
| 検索モード（第6） | -1 | 末尾から先頭へ検索 |
| 検索モード（第6） | -2 | バイナリ検索（範囲が昇順のとき） |
| 検索モード（第6） | 2 | バイナリ検索（範囲が降順のとき） |
自分が実務でよく使うのは検索モードの -1 です。日付順に追記していく履歴表で、「同じ取引先の最新の1件」を取りたいときに、末尾から検索させれば一発で最後の行が取れます。
=XLOOKUP($D2, $A$2:$A$500, $C$2:$C$500, "履歴なし", 0, -1)
VLOOKUPは常に上から探して最初に一致した行を返すため、同じことをやろうとすると別の関数と組み合わせる必要があります。
複数の列をまとめて取り出せる
XLOOKUPは戻り範囲に複数列を指定でき、その場合は結果が横方向に自動で展開（スピル）されます。
=XLOOKUP($D2, $A$2:$A$100, $B$2:$E$100, "")
これだけで氏名・部署・入社日・内線番号を一度に取り出せます。VLOOKUPで同じことをするなら4つの数式を並べることになるので、行数が多い表ほど差が出ます。
VLOOKUPとXLOOKUPの違い
| 項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 検索方向 | 検索値より右側の列のみ | 左右どちらの列も検索可能 |
| デフォルトの一致方法 | 近似一致（要注意） | 完全一致（安全） |
| 列の挿入・削除への耐性 | 列番号がずれると崩れる | 列範囲で指定するため崩れにくい |
| 見つからない場合の処理 | 別途IFERROR等で対応が必要 | 引数内で直接指定できる |
| 検索の向き | 常に上から下 | 下から上も指定できる |
| 複数列の取得 | 数式を列の数だけ書く | 1つの数式でまとめて返せる |
| 利用できるバージョン | 古いExcelでも利用可能 | Microsoft 365・Excel 2021以降のみ |
XLOOKUPが優れている点
XLOOKUPの一番の利点は、検索値より左側の列も検索できることです。VLOOKUPでは「検索値を左端に配置し直す」といった表の作り直しが必要になる場面でも、XLOOKUPならそのまま対応できます。
また、デフォルトが完全一致である点も安全性が高く、VLOOKUPで頻発しがちな近似一致の指定忘れによるミスを防げます。「見つからない場合」の値を引数内で直接指定できるため、IFERROR(VLOOKUP(...), "該当なし") のような二重の関数を書かずに済むのも実務上のメリットです。
VLOOKUPを使うべき場面
一方で、XLOOKUPは比較的新しい関数のため、古いバージョンのExcel（2019以前など）では使用できません。取引先や社内で共有するファイルが、どのバージョンのExcelで開かれるかわからない場合は、互換性の高いVLOOKUPを選ぶ方が安全です。自分の職場でも、社外に配布する集計フォーマットにはあえてVLOOKUPを使うようにしています。
XLOOKUPを含むファイルを古いExcelで開くと、数式が _xlfn.XLOOKUP という見慣れない形に変わり #NAME? エラーになります。「自分のPCでは動くのに、相手先で開いたら壊れていた」という事故はこれが原因です。配布用のファイルを作るときは、環境を確認するか、最初からVLOOKUPで書いておくのが安全です。
近似一致（TRUE）が役に立つ場面
「検索方法は常にFALSE」と書きましたが、近似一致にも正しい使いどころがあります。代表的なのが、区切りのある段階表から値を引くケースです。
| 下限点数 | 評価 |
|---|---|
| 0 | D |
| 60 | C |
| 70 | B |
| 90 | A |
この表をA列・B列に置いておけば、点数から評価を引くのは次の1本で済みます。
=VLOOKUP($D2, $A$2:$B$5, 2, TRUE)
75点なら「70の行」に一致し、Bが返ります。所得に応じた税率、購入金額に応じた送料など、実務でも使う場面はあります。
ただし条件が2つあります。検索する列が昇順に並んでいることと、区切りの下限値で表を作ることです。並び順がばらばらだと、エラーも出さずに間違った値を返すので、近似一致を使う表は必ず並べ替えてから使ってください。XLOOKUPなら第5引数に -1 を指定すると同じことができ、こちらは並び順に依存しない分だけ安全です。
#N/A エラーが出るときのチェックリスト
VLOOKUPで一番多いつまずきが #N/A です。「表には確かにその値があるのに一致しない」というときは、次の順に確認していくとほぼ解決します。
| 原因 | 症状 | 対処 |
|---|---|---|
| 数値と文字列の違い | 見た目は同じ「1001」なのに一致しない | 片方が文字列。セルの表示形式を揃えるか、VALUE() / TEXT() で型を合わせる |
| 余分なスペース | 末尾に半角スペースが入っている | TRIM() で除去してから検索する |
| 全角と半角の違い | 「ABC」と「ＡＢＣ」 | ASC() で半角に統一する |
| 範囲がずれた | 下の行だけエラーになる | 範囲を $ で絶対参照にする |
| 検索値が左端にない | 常にエラー | 表を組み替えるか、XLOOKUP／INDEX+MATCHに変える |
| 検索値が本当にない | 新規データなど | IFNA() で「該当なし」を表示させる |
| 別シート参照のずれ | シート名変更後に崩れる | シート名を含む参照を書き直す |
型の違いは目視では判断できないので、確認用のセルに =ISNUMBER(A2) と入れて TRUE / FALSE を見比べるのが手っ取り早いです。片方だけ FALSE なら、それが原因です。
IFERRORとIFNAの使い分け
エラーを隠すときは IFERROR がよく使われますが、これは #N/A だけでなく #REF! や #VALUE! まで全部まとめて隠してしまいます。数式そのもののミスに気づけなくなるので、検索関数では IFNA のほうが向いています。
=IFNA(VLOOKUP($D2, $A$2:$B$100, 2, FALSE), "該当なし")
こう書いておけば、「データが無い」ことによる #N/A だけを「該当なし」に置き換え、範囲指定のミスなど本当の不具合はエラーのまま表示されます。
複数の条件で検索したいとき
「支店名と商品名の両方が一致する行」を探したい、というのは実務でよく出てきます。
VLOOKUPには複数条件の引数がないので、作業列を1本作って条件を連結するのが定番の方法です。表の左端に列を挿入し、=B2&"_"&C2 のようなキーを作っておけば、検索値も同じ形（=$F2&"_"&$G2）で作って普通に検索できます。区切り文字を挟むのは、「東京」＋「1」と「東」＋「京1」が同じ文字列になってしまう事故を防ぐためです。
XLOOKUPなら作業列なしで書けます。
=XLOOKUP(1, ($B$2:$B$100=$F2)*($C$2:$C$100=$G2), $D$2:$D$100, "該当なし")
条件の掛け算で、両方成立する行だけが1になる仕組みです。少し難しく見えますが、表を改造せずに済むのは大きな利点です。
INDEX+MATCHはまだ必要か
XLOOKUPが使えない環境で、なおかつ検索値より左の列を取りたい。この条件のときに登場するのが INDEX と MATCH の組み合わせです。
=INDEX($A$2:$A$100, MATCH($D2, $C$2:$C$100, 0))
MATCHが「検索値が上から何番目か」を返し、INDEXがその位置の値を取り出す、という二段構えです。Excel 2019以前の環境が残っているうちは覚えておく価値がありますが、これから学ぶ人はXLOOKUPを優先し、INDEX+MATCHは「古いファイルを読めればいい」くらいの位置づけで問題ありません。
Googleスプレッドシートでの扱い
GoogleスプレッドシートでもXLOOKUPは使えます。一致モード（0／1／-1／2）も検索モード（1／-1／-2／2）もExcelと同じ指定で動くので、両方を行き来する場合でも書き方を覚え直す必要はありません。
ただし、ExcelファイルをスプレッドシートにアップロードしたりXLSX形式で書き出したりすると、環境によっては関数が正しく引き継がれないことがあります。ファイルをまたぐ運用をしているなら、変換後に検索結果の数件を目視で突き合わせる習慣をつけておくと安心です。スプレッドシート側の使い方はGoogleスプレッドシートとの連携と共同編集の基本にまとめています。
どちらを選ぶべきか
| 状況 | おすすめの関数 |
|---|---|
| 自分だけ、または最新環境の社内で使うファイル | XLOOKUP |
| 社外配布・古いExcel環境が混在する可能性がある | VLOOKUP |
| 検索値より左側の列を参照したい | XLOOKUP |
| 同じキーの最後の1件（最新履歴）を取りたい | XLOOKUP（検索モード -1） |
| 複数の列をまとめて取り出したい | XLOOKUP |
| 段階表から評価や税率を引きたい | どちらでも可（VLOOKUPはTRUE、XLOOKUPは一致モード -1） |
| 古い環境で左側の列を参照したい | INDEX+MATCH |
| とりあえず基本を押さえたい初心者 | VLOOKUPから覚えて、余裕が出たらXLOOKUPへ |
自分がおすすめする学習の順番は、まずVLOOKUPの基本と落とし穴（近似一致の危険性）をしっかり理解し、そのうえでXLOOKUPの利便性を体感する、という流れです。VLOOKUPの制約を知っているからこそ、XLOOKUPのありがたみもより実感できます。
覚える順番
- VLOOKUPの4つの引数を、完全一致（FALSE）で書けるようにする
- 範囲を
F4で絶対参照にする癖をつける #N/Aが出たら、型・スペース・範囲の3点を順に確認するIFNAで「該当なし」を出せるようにする- XLOOKUPで同じ処理を書き換えてみる
- 一致モード・検索モードと、複数列のスピルを試す
- 表の作り方そのものを見直す（ピボットテーブルが使える形にしておくと、検索関数を書く場面自体が減ります）
検索関数がうまく動かない原因の多くは、関数の書き方ではなく元データの作り方にあります。1行1件、見出しは1行だけ、セル結合はしない。この3つを守るだけで、VLOOKUPもXLOOKUPも素直に動くようになります。基本的な関数の考え方はExcel関数の基本も参考にしてください。
まとめ
VLOOKUPとXLOOKUPは、どちらも「表からデータを検索する」という目的は同じですが、検索方向・デフォルトの一致方法・対応バージョンに大きな違いがあります。自分の使う環境や共有相手のExcelバージョンに応じて、適切な方を選んでみてください。
そして、どちらを使うにしても最初に覚えるべきは「範囲を絶対参照にする」ことと「#N/A の原因を切り分けられるようにする」ことです。この2つを押さえておけば、検索関数で立ち往生する時間はぐっと減ります。
しーとちゃん
完全一致をデフォルトにできるの、地味にありがたいですね。うちは社外配布もあるので、まずはVLOOKUPの完全一致指定を徹底します！
けんじ
それが一番堅実な選択だと思うよ。あと、エラーが出たときは数式を疑う前に「型・スペース・範囲」の3つを見る癖をつけてね。環境が整ってきたら、XLOOKUPも積極的に取り入れてみて。
最後までお読みいただきありがとうございました。
ご質問やご感想がありましたら、お問い合わせフォームよりお気軽にご連絡ください。