サービス
サービスを探す
プロ人材を探す
仕事を探す
ブログを探す
VLOOKUPをXLOOKUPに置き換える理由と、安全な移行手順
記事IT・テクノロジー
商品マスタに列を1本挿入しただけなのに、集計表の単価が全部おかしくなった。
エラーは出ていない。数字が入っているので、しばらく気づかなかった。
VLOOKUPを使っていると、いつかこれに当たります。
使い方が悪いのではなく、VLOOKUPの構造上そうなります。
■ VLOOKUPの弱点は3つ
1. 列番号が「何番目か」という数字で固定されている
=VLOOKUP(A2, 商品マスタ!A:D, 4, FALSE)
この「4」は「範囲の左から4列目」という意味で、
どの見出しの列か、という情報は持っていません。
マスタのB列とC列の間に1列挿入すると、4列目にあった単価は5列目に動きます。
しかし数式は4のままなので、単価ではなく別の列の値を静かに返します。
列を削除して範囲外になれば #REF! になるので気づけますが、
挿入した場合はエラーになりません。気づけない方の壊れ方です。
2. 検索列より左の列を取れない
VLOOKUPは範囲の1列目を検索して、そこから右にある列を返します。
商品コードで検索して、その左にある「分類」を取りたい——これができません。
そのため、マスタの列を並べ替えたり同じ列を複製したりという、
本来不要な加工が発生します。
3. 見つからなかったときの処理が書けない
該当なしのときは #N/A が返ります。消すには数式全体を囲むしかありません。
=IFERROR(VLOOKUP(A2, 商品マスタ!A:D, 4, FALSE), "")
一見きれいですが、これは「該当なし」だけでなく、列番号ずれによる #REF! も
まとめて空白にします。 異常が起きていることが、画面から消えます。
■ XLOOKUPはこの3つを解決する
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
先ほどの例は、こう書き換わります。
=XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!D:D, "該当なし")
違いは3点です。
列番号がない。 「D列を返す」と範囲そのもので指定するので、
マスタに列を挿入しても参照は自動で追従します。ずれません。
検索範囲と戻り範囲が独立している。
戻り範囲が検索範囲より左でも動きます。マスタを加工する必要がなくなります。
第4引数に、見つからないときの値を直接書ける。
「該当なし」と表示しつつ、それ以外のエラーは隠れずに表示されます。
もう1つ、既定が完全一致です。
第4引数の書き忘れで近似一致になる事故が起きません。
■ 移行するときの注意
XLOOKUPは、すべての環境で使えるわけではありません。
・Microsoft 365版のExcel、Excel 2021以降 … 使える
・Excel 2019、2016、2013 … 使えない（#NAME? になります）
・Excel for the web … 使える
・Googleスプレッドシート … 使える（2022年に追加されました）
問題はファイルを渡す相手の環境です。
自分のExcelでは動いても、Excel 2019の取引先が開くと #NAME? が並びます。
社外に配布するファイルや、社内でバージョンが揃っていない場合は
INDEX+MATCHを使ってください。どのバージョンでも動き、左方向も検索できます。
=INDEX(商品マスタ!D:D, MATCH(A2, 商品マスタ!A:A, 0))
MATCHの第3引数の 0 が完全一致です。省略すると近似一致になります。
もう1点、XLOOKUPは検索範囲と戻り範囲の行数が揃っていないと #VALUE! になります。
A2:A100 と D2:D200 のような書き方は動きません。行数を揃えてください。
■ 置き換えの手順
いきなり全部を置換しないでください。
1. 今の列番号が何を指しているか、書き出す
=VLOOKUP(..., 4, FALSE) の 4 が、左から数えてどの見出しなのか。
すでにずれている可能性があるので、現物を数えてください。
移行前に間違っていたものを、そのまま移行しても直りません。
2. 1本だけ置き換えて、隣の列で突き合わせる
元のVLOOKUPの列を残し、隣にXLOOKUPを入れて差分を見ます。
=IF(B2=C2, "", "差分")
このIF列を最終行までコピーし、「差分」が出ないことを確認します。
3. 確認が取れてから、残りを置換する
4. 元の列を消すのは、最後
消した後に問題が見つかると比較対象が無くなります。
1日か2日は両方を並べて運用してください。
■ やってはいけないこと
置換ダイアログで VLOOKUP を XLOOKUP に一括置換すること。
引数の並びも数も違うので動きません。
=XLOOKUP(A2, 商品マスタ!A:D, 4, FALSE) は範囲の指定として成立していません。
100本の数式が一斉にエラーになり、元に戻すのも手作業になります。
そして、移行後の #N/A をIFERRORで消して片付けたことにしないでください。
この #N/A は、マスタ側にデータが無いか、
検索値に余分な空白が混ざっているかのどちらかです。
前者はマスタの整備が必要で、後者は TRIM() で解決します。
どちらも、消してしまうと後で数字が合わなくなります。
■ それでも解決しないとき
・数式が何百本もあり、どこから手を付けるか判断がつかない
・置き換えたら値が変わったが、どちらが正しいのか分からない
・作った人がおらず、そもそも何を引いている表なのか読めない
このあたりまで来たら、上書きしながら進めるより一度棚卸しした方が早いです。
私はExcel・スプレッドシートの数式の調査と修正を承っています。
修正前後の突き合わせ結果と、どの数式がどう間違っていたかの一覧を添えてお返ししています。
――
この記事の執筆には生成AIを活用しています。内容は実際の挙動を確認して書いています。
このブログを見た人にオススメ
