Excelで表を検索するとき、VLOOKUPを使っていて「列を増やしたら壊れた」「左側の値を取れない」「式が分かりにくい」と困ることがありますよね。そんなときは、まず結論としてXLOOKUPを使える環境ならXLOOKUPを選ぶのがおすすめです。VLOOKUPより式が読みやすく、列番号の指定ミスが減り、左方向の検索や見つからないときの表示指定までまとめてできます。ここでは、違いと使い分けを仕事ですぐ使える形でサクッと整理していきます。
XLOOKUPとVLOOKUPの違いを先に結論で確認
- VLOOKUP：指定した表の左端列から検索し、右側の列の値を返します。
- XLOOKUP：検索する列と返す列を別々に指定できるので、左右どちらでも検索できます。
特に実務で大きいのは、XLOOKUPは表の途中に列を追加しても壊れにくいことです。VLOOKUPは「何列目を返すか」を数字で指定するため、表の構成が変わると結果がズレやすいんですね。
違いをひと目で整理
- 左方向の検索：VLOOKUPは苦手、XLOOKUPは可能
- 列番号の指定：VLOOKUPは必要、XLOOKUPは不要
- 見つからない場合の表示：VLOOKUPはIFERRORと組み合わせがち、XLOOKUPは式内で指定可能
- 式の読みやすさ：XLOOKUPのほうが直感的
- 対応バージョン：VLOOKUPは広く使える、XLOOKUPは新しいExcelで利用可能
まずはこれだけ覚えればOKな使い方
たとえば、A列に社員番号、B列に氏名、C列に部署がある表から、社員番号をもとに部署を取り出したいとします。
XLOOKUPの例
=XLOOKUP(E2,A:A,C:C,"見つかりません")
- E2：検索したい社員番号
- A:A：社員番号が入っている検索範囲
- C:C：返したい部署の範囲
- "見つかりません"：該当データがないときの表示
VLOOKUPの例
=VLOOKUP(E2,A:C,3,FALSE)
- E2：検索したい社員番号
- A:C：検索対象の表全体
- 3：A列から数えて3列目のC列を返す
- FALSE：完全一致で検索
見比べると、XLOOKUPのほうが「どこを探して、どこを返すか」が分かりやすいですよね。メンテナンスのしやすさでもXLOOKUPが有利です。
VLOOKUPからXLOOKUPに置き換える手順
今のシートでVLOOKUPを使っているなら、次の流れで置き換えるとスムーズです。
- 既存のVLOOKUP式をクリックして確認します。
- 「検索値」「検索する列」「返したい列」を切り分けて考えます。
- VLOOKUPの表範囲ではなく、XLOOKUPでは検索範囲と戻り範囲を別々に指定します。
- 必要なら4つ目の引数に「"該当なし"」などの表示を入れます。
- Enterキーで確定し、下までオートフィルします。
たとえばVLOOKUPで=VLOOKUP(E2,A:C,2,FALSE)となっていたら、XLOOKUPでは=XLOOKUP(E2,A:A,B:B,"該当なし")のように書けます。
XLOOKUPのメリットを実務目線で紹介
1. 左側の列も返せる
VLOOKUPは左端列で検索して右側の値を返す仕組みなので、たとえば「氏名から社員番号を返す」といった左方向の検索が苦手です。XLOOKUPなら検索範囲と戻り範囲を自由に指定できるので、この制約がありません。
2. 列を追加してもズレにくい
VLOOKUPは「3列目」「4列目」のように数字で返す列を指定します。そのため、途中に列を追加すると結果が変わることがあります。XLOOKUPは返す列そのものを指定するので、表変更に強いです。
3. エラー対応が1つの式で済む
VLOOKUPで見つからないと#N/Aになり、見た目が気になる場合はIFERRORを重ねることが多いです。XLOOKUPなら見つからない場合の表示を最初から書けるので、式がすっきりします。
4. 式を見たときに意味が分かりやすい
引き継ぎや自分の数か月後の作業を考えると、式の読みやすさはかなり大事です。XLOOKUPは「どこで探し、どこを返すか」が見てすぐ分かるので、修正もしやすいですよ。
VLOOKUPを使う場面はある？
あります。主に古いExcel環境との互換性が必要なときです。社内PCや取引先とのファイル共有でXLOOKUPが使えない環境なら、VLOOKUPのほうが安全なことがあります。
- 古いExcelで開く可能性が高い
- すでにVLOOKUP前提のテンプレートが社内で回っている
- 簡単な右方向検索だけで十分
ただし、自分の環境でXLOOKUPが使えるなら、これから新規で作る表はXLOOKUP優先で問題ありません。
実際の入力手順を初心者向けに解説
- 結果を表示したいセルをクリックします。
- キーボードで「=XLOOKUP(」と入力します。
- 検索したい値が入っているセルをクリックします。
- カンマを入力し、検索元の列をドラッグして指定します。
- もう一度カンマを入力し、返したい列をドラッグして指定します。
- 必要ならさらにカンマを入力し、「"見つかりません"」のように入力します。
- 最後に「)」を入力してEnterキーを押します。
数式バーを見ながら入力すると、どの引数を入れているか確認しやすいです。列全体を指定しても使えますが、データ量が多いファイルでは必要範囲だけに絞ると軽くなることもあります。
うまくいかない場合のチェックポイント
#NAME? エラーが出る
XLOOKUP非対応のExcel環境の可能性があります。まずはExcelのバージョンを確認しましょう。使えない場合はVLOOKUPやINDEX/MATCHで代用します。
#N/A になる
- 検索値と検索範囲の値が一致していない
- 全角と半角が混ざっている
- 数値と文字列が混在している
- 前後に余計なスペースが入っている
見た目が同じでも一致しないことがあるので、TRIMやVALUEの利用も検討しましょう。
違う値が返る
検索範囲と戻り範囲の行数がズレていないか確認してください。A2:A100で検索しているのに、戻り範囲がC3:C101だと正しく返りません。
VLOOKUPから置き換えたら結果が変わった
VLOOKUPで近似一致を使っていた可能性があります。VLOOKUPの最後の引数がFALSEかTRUEかを確認し、元の式の挙動をそろえましょう。通常の名簿やマスター検索では完全一致を使うことが多いです。
どちらを選ぶべきか迷ったときの判断基準
- 新しいExcelで自分や社内が使う：XLOOKUP
- 古いExcel互換が最優先：VLOOKUP
- 表の列追加や変更が多い：XLOOKUP
- 単純な検索を急ぎで入れるだけ：どちらでもOK、ただし今後を考えるならXLOOKUP
迷ったら、まずはXLOOKUPで考えるクセをつけると、後からの修正がかなりラクになりますよ。
最後に覚えておきたい時短ワザ
テーブル化して範囲指定をラクにする
検索元データをテーブル化しておくと、列名で分かりやすく数式を書けます。
- 表の中のセルをクリックします。
- Ctrl + T を押します。
- 「先頭行をテーブルの見出しとして使用する」を確認してOKを押します。
これでデータ追加にも強くなります。
数式のオートフィルを素早く行う
式を入力したセルの右下にマウスを合わせ、ダブルクリックすると、隣接データに合わせて下まで一気にコピーできることがあります。件数が多い表ではかなり時短になります。
XLOOKUPは、VLOOKUPに慣れている人ほど「もっと早く使えばよかった」と感じやすい関数です。まずは1つの既存表だけでも置き換えてみると、違いがすぐ実感できます。仕事中の検索作業を少しでもラクにしたいなら、今日からXLOOKUPを試してみましょう。
