目的別
VLOOKUPとXLOOKUPの違い
VLOOKUPとXLOOKUPは、どちらも「表から値を探して取り出す」関数です。やることは同じですが、XLOOKUPはVLOOKUPの弱点をひと通り解消した後継として作られました。
結論から言うと、新しく書くならXLOOKUPです。ただし、VLOOKUPを使うべき場面も残っています。この記事ではその判断基準を整理します。
このページの中身（18項目）
書き方の比較
同じ処理を両方で書きます。
VLOOKUP
=VLOOKUP("B-201", A2:C100, 2, FALSE)XLOOKUP
=XLOOKUP("B-201", A2:A100, B2:B100)VLOOKUPは「範囲の左から2番目」と位置で指定し、XLOOKUPは「A列を探してB列を返す」と範囲で指定します。
7つの違い
| 観点 | VLOOKUP | XLOOKUP |
|---|---|---|
| 左方向の検索 | できない | できる |
| 列の挿入 | 壊れる | 壊れない |
| 見つからない場合 | IFERROR が必要 | 引数で指定できる |
| 複数列の取得 | 数式を複数書く | 1つで返せる |
| 下から検索 | できない | できる |
| 処理の重さ | 範囲全体を読む | 2列だけ読む |
| 古いExcel | 動く | 動かない |
1. 左方向の検索
VLOOKUPは範囲の左端列しか検索できません。 「商品名から商品コードを引く」ことができません。
XLOOKUPは検索範囲と結果範囲が独立しているので、位置関係を問いません。
=XLOOKUP("ハサミ", B2:B100, A2:A100)VLOOKUPで左方向を検索するには INDEX+MATCH が必要でした。XLOOKUPはそれを1つの関数で置き換えます。
2. 列の挿入で壊れるか
これが実務で最も痛い違いです。
VLOOKUPは「左から3番目」という位置で指定します。表の途中に列を挿入すると、数式が別の列を指してしまいます。エラーにならず、間違った値を返すのが厄介です。
XLOOKUPは範囲そのものを指定するので、列を挿入しても範囲が自動で追従します。
運用中に静かに壊れないという点だけでも、XLOOKUPを選ぶ理由になります。
3. 見つからないときの扱い
VLOOKUPは #N/A を返すので、IFERROR で包む必要があります。
=IFERROR(VLOOKUP(E2, A:C, 2, FALSE), "未登録")XLOOKUPは4つ目の引数で直接指定できます。
=XLOOKUP(E2, A:A, B:B, "未登録")この差は見た目以上に重要です
IFERROR は数式全体のエラーを握りつぶすので、範囲指定のミスや #REF! まで「未登録」にしてしまいます。
XLOOKUPの第4引数は「見つからなかった場合」だけを扱うため、本当の不具合はエラーとして表面化します。 詳しくは IFERROR のページで扱っています。
4. 複数の列をまとめて返す
XLOOKUPは結果範囲を複数列にできます。
=XLOOKUP("B-201", A2:A100, B2:D100)商品名・価格・在庫が横並びで一度に出ます。VLOOKUPだと3つの数式が必要でした。
5. 下から検索する
同じキーが複数あるとき、VLOOKUPは常に最初の1件を返します。
XLOOKUPは6つ目の引数に -1 を指定すると、最後の1件を返します。
=XLOOKUP(E2, A:A, B:B, "", 0, -1)追記型の履歴表から最新の状態を取り出すという、実務で頻出の処理が1行で書けます。
6. 処理の重さ
VLOOKUPは指定した範囲全体を読みます。A:Z を指定すれば26列すべてです。
XLOOKUPは検索列と結果列の2列しか読みません。行数が数万規模になると体感で差が出ます。
7. 古いExcelとの互換性
ここだけがVLOOKUPの優位点です。
XLOOKUPは Microsoft 365 と Excel 2021 以降でしか動きません。Excel 2019以前で開くと #NAME? になります。
スプレッドシートでは両方とも問題なく使えます。
どちらを使うべきか
| 状況 | 選ぶ関数 |
|---|---|
| スプレッドシートで新規に書く | XLOOKUP |
| Excel 365 / 2021以降で新規 | XLOOKUP |
| Excel 2019以前と共有する | VLOOKUP |
| 取引先にxlsxで渡す（環境不明） | VLOOKUP |
| 既存の数式を保守する | 触らずVLOOKUPのまま |
| 大量データ（数万行以上） | XLOOKUP |
判断基準は互換性の一点です。相手の環境が分からないファイルを配るならVLOOKUP、それ以外はXLOOKUPと考えて構いません。
VLOOKUPを使い続けるなら
互換性のためにVLOOKUPを使う場合、列番号を MATCH で動的にすると、列の挿入で壊れなくなります。
=VLOOKUP($E2, $A:$D, MATCH(F$1, $A$1:$D$1, 0), FALSE)見出し行から列位置を計算しているので、列順が変わっても追従します。VLOOKUPの最大の弱点を、互換性を保ったまま潰せます。
また、4つ目の引数 FALSE は必ず書いてください。 省略すると近似一致になり、存在しない値でもエラーにならず、間違った値が返ります。詳しくは VLOOKUP のページで扱っています。
移行するときの注意点
古い関数から新しい関数へ書き換える際、機械的に置き換えると事故が起こります。引数の意味が違うためです。
古い関数は、検索する列を含む表全体を範囲として渡し、何列目を返すかを数字で指定します。新しい関数は、検索する列と返す列を別々に渡します。範囲の取り方がまったく違うので、単純な置き換えはできません。
また、古い関数で近似一致を使っていた場合、新しい関数では一致モードの指定が必要です。省略すると完全一致になるため、結果が変わります。料金表のような区分の判定に使っていた場合、この違いに気づかないと数字が合わなくなります。
エラー回避の関数で包んでいた場合、新しい関数では引数として指定できます。包んだままでも動きますが、数式が無駄に長くなります。
書き換えたら、必ず旧数式の結果と突き合わせてください。数行だけでも確認すれば、引数の取り違えに気づけます。全件を一度に書き換えて、後から違いに気づくのがもっとも困る展開です。
既存のVLOOKUPをXLOOKUPに置き換えるとき、引数の意味が変わる点に注意してください。
VLOOKUP(キー, 範囲全体, 列番号, FALSE)
XLOOKUP(キー, 検索列だけ, 結果列だけ)VLOOKUPの「範囲全体」をそのままXLOOKUPの第2引数に入れると動きません。検索する列だけを指定してください。
選ぶ基準は「環境」と「壊れやすさ」
どちらを使うべきかという問いには、二つの観点から答えが出ます。使える環境かどうかと、壊れにくさをどこまで求めるかです。
新しい検索関数が使える環境で、新規に作るファイルなら、迷う必要はありません。新しいほうを選んでください。列の挿入で壊れず、完全一致が既定で、見つからないときの処理も組み込まれています。古い関数を選ぶ理由がありません。
一方、ファイルを共有する相手の環境が古い場合は、話が変わります。新しい関数を含むファイルを古い環境で開くと、数式がエラーとして表示されます。相手が編集して保存すると、数式そのものが失われることもあります。この場合は、古い関数か、どの環境でも動く組み合わせを選ぶことになります。
つまり、自分の環境ではなく、そのファイルが開かれる可能性のある環境すべてを基準に選ぶ必要があります。社内で配布するファイルなら、もっとも古い環境に合わせるのが安全です。
既存ファイルを書き換えるべきか
すでに古い関数で作られたファイルを、書き換えるべきかという判断もあります。
原則として、動いているものを急いで書き換える必要はありません。書き換え自体がリスクを持ちます。範囲の指定を間違える、条件の指定を落とす、といった事故が起こりえます。
書き換える価値があるのは、次のような場合です。
過去に列の挿入で壊れた経験があるファイル。同じ事故が繰り返される可能性が高いので、構造的に防げる形にする価値があります。
左方向の検索のために、列の順番を無理に変えているファイル。新しい関数なら制約がなくなるので、データの持ち方を自然な形に戻せます。
エラー回避の関数で包んだ数式が大量にあるファイル。新しい関数なら包む必要がなく、数式が短くなります。本当のエラーを隠してしまう危険も減ります。
書き換える場合は、一度に全部やらないでください。一箇所を書き換えて動作を確認し、それから広げます。控えを取ってから作業するのは当然です。
どの環境でも動く選択肢
古い環境でも動き、かつ列の挿入で壊れない方法があります。位置を求める関数と、位置で取り出す関数の組み合わせです。
この組み合わせは、検索する列と返す列を別々に指定します。列番号を数える必要がないので、列を挿入しても壊れません。左方向の検索も制約なくできます。新しい関数と同じ利点を、古い環境でも得られます。
欠点は、数式が長くなり、初見では何をしているか読み取りにくいことです。二つの関数が入れ子になっているため、構造を知らないと理解できません。
したがって、他人が触るファイルで使う場合は、補助的な説明を残しておくと親切です。あるいは、位置を求める部分を別の列に出して、段階的に処理する形にすれば読みやすくなります。
三つの選択肢を整理すると、こうなります。新しい環境で新規に作るなら新しい関数。古い環境も想定するが壊れにくさを求めるなら組み合わせ。単純で読みやすさを優先するなら古い関数。この順で検討してください。
どちらを使うか迷ったときの判断
二つの関数のどちらを使うかは、次の順で判断すると迷いません。
XLOOKUPが使える環境なら、XLOOKUPを使ってください
列番号の指定が不要で、列の追加や削除で壊れません。見つからなかったときの値も引数で指定できます。
共有先の環境が古い可能性がある場合は、VLOOKUPを使ってください
対応していない環境で開くと、数式がエラーになります。社外に渡すファイルでは、この点が判断を分けます。
既存の表を触る場合は、書き換えないでください
動いているVLOOKUPをXLOOKUPに変える作業は、事故のもとです。新しく作る部分から切り替えてください。
左方向の検索が必要な場合は、XLOOKUPかINDEX+MATCHです。 VLOOKUPでは書けません。
複数の値を返したい場合は、XLOOKUPかFILTERです
VLOOKUPは一つの値しか返しません。
そして、どちらを使ったかをシート内に残してください
数式の意図が分かる注記が一行あるだけで、引き継ぎが楽になります。
「目的別」の関数を全体から見る
目的別に含まれる関数を、全体から見ておきます。同じカテゴリの関数は、つまずく場所も似ています。この記事のほかに52本あります。
このカテゴリで共通するつまずき
関数名から探すより、条件から絞るほうが早く決まります。1つで理解した内容は、同じカテゴリの他の関数にもそのまま使えます。
どれを使うかの決め方
出したい結果を一文で書けば、関数はほぼ1つに決まります。関数の一覧を眺めて選ぼうとすると、どれも当てはまるように見えて決まりません。
迷ったときは
→ 全関数の一覧 に目的別の索引があります。
数式を書く前の手順
手順を分けると、どこで詰まっているかが分かります。
1. 出したい結果を一文で書く
「何を、どの条件で、どんな形で出したいか」。この一文が書ければ、関数はほぼ決まります。書けないなら、まだ要件が固まっていません。
2. 元データの型を確認する
関数名から探すより、条件から絞るほうが早く決まります。=ISNUMBER(A2) で数値かどうかを確認できます。見た目が同じでも、数値と文字列は別物です。
3. 空欄と0の扱いを決める
未入力なのか、実績が0なのか。表の意味が変わります。どちらとして扱うかを決めてから書いてください。
4. 条件の数を数える
出したい結果を一文で書けば、関数はほぼ1つに決まります。将来条件が増える可能性があるなら、最初から複数条件に対応する形にしてください。
5. 共有先の環境を確認する
新しい関数は、古い環境で開くとエラーになります。社外に渡すファイルでは、この一点で選択肢が変わります。
6. 1行だけ書いて確認する
全行にコピーする前に、1行分の結果が正しいかを見てください。間違ったまま広げると、直す手間が増えます。
どの関数でも共通する確認
以下は、どの関数を使っていても同じです。
参照を固定したか
下方向にコピーする数式では、動いてはいけない参照を固定します。上の数行だけ見て判断しないでください。ずれは下のほうで表面化します。
範囲の行数が揃っているか
複数の範囲を渡す関数では、行数がずれると結果が狂います。エラーにならず値が返るため、気づきにくい種類の間違いです。
型が揃っているか
=ISNUMBER(A2)数値として入っているかを判定します。システムから書き出したデータでは、これが原因のことが多くあります。
前後に空白が入っていないか
=LEN(A2)&" / "&LEN(TRIM(A2))数が違えば、余分な空白が入っています。見た目では判別できません。
別の方法で検算したか
同じ数字を別の関数でも出して、突き合わせてください。1つの結果だけを見ても、正しいかどうかは判断できません。
最終行まで確認したか
一番下までスクロールして、結果を見てください。途中から値が変わっていないかを確認します。
引き継ぐときに残すこと
数式は動いていても、意図は残りません。渡す前に、次の点をシート内にメモしてください。3分で終わります。
何を出している数式か
一行で構いません。読めば分かると思っても、数か月後の自分には分かりません。
どの表を参照しているか
参照先のシート名と、更新の担当。別ファイルなら、その場所も書いてください。
該当しないときの扱い
空欄にしているのか、文言を出しているのか、エラーのまま残しているのか。書いていないと、受け取った側は空欄をデータなしと読みます。
触ってはいけない場所
列を挿入すると壊れる数式がある場合、必ず書き残してください。この一行があるだけで、引き継ぎ後の事故が大きく減ります。
使った関数の対応環境
新しい関数を使っている場合、古い環境では開けません。共有の範囲が広がる可能性があるなら、明記してください。
確定した期間は値にする
締めた月の集計は、値貼り付けにしてください。参照元を消しても壊れなくなり、ファイルも軽くなります。
このサイトの収録状況
全201本を、次のカテゴリに分けて収録しています。
| カテゴリ | 記事数 |
|---|---|
| 目的別 | 51本 ←この記事 |
| 集計 | 47本 |
| 文字列 | 31本 |
| 日付 | 19本 |
| 検索・参照 | 15本 |
| 条件 | 15本 |
| 機能 | 12本 |
| 配列・抽出 | 10本 |
| 関数一覧 | 1本 |
探し方の順番
関数名が分かっているなら検索窓から。やりたいことだけ決まっているなら「目的別」から。近い関数を比べたいなら、同じカテゴリの一覧から入ってください。
記事の構成
どの記事も、書式・実例・つまずきやすいところ・似た関数との使い分けを同じ順番で並べています。1本読めば、他の記事も同じ場所を探せます。
よくある質問
Q. 新しい関数が使えません
環境が対応していません。位置を求める関数と取り出す関数の組み合わせで代替してください。
Q. 古い環境で開くとどうなりますか
数式がエラーとして表示されます。相手が保存すると、数式が失われることもあります。
Q. 速度に違いはありますか
複数列を一度に返せる分だけ新しい関数が有利ですが、劇的な差ではありません。速度が問題なら、範囲の指定を見直すほうが効果があります。
Q. 左方向の検索はどうすればいいですか
新しい関数なら制約なくできます。古い環境では、位置を求める関数と取り出す関数の組み合わせを使ってください。
Q. 複数条件で検索したいのですが
どちらの関数でも、検索キーを連結する書き方があります。ただし、マスタ側に結合列を作るほうが保守しやすくなります。
Q. 該当が複数ある場合は
どちらも一件しか返しません。全件を取り出したい場合は、抽出の関数を使ってください。
Q. どちらを覚えるべきですか
新しいほうを覚えてください。ただし、既存ファイルには古い関数が大量に残っているので、読めることは必要です。
Q. 書き換えの優先順位は
列の挿入で壊れた経験があるファイルから手を付けてください。効果がもっとも大きくなります。
この分野の他の記事
同じ「目的別」の記事です。目的が近いので、こちらで解決しない場合はこちらも見てください。
まとめ
- 新規に書くならXLOOKUP。列の挿入で壊れないのが最大の理由
- 左方向の検索・複数列の取得・下から検索ができる
- VLOOKUPを選ぶ理由は互換性だけ（Excel 2019以前）
- VLOOKUPを使うなら、列番号を MATCH で動的にする
- VLOOKUPの4つ目の引数
FALSEは必ず書く