ExcelのVLOOKUPとXLOOKUP｜別の表から値を引く書き方と、#N/Aになる原因の直し方

重複と近似一致、エラー表示の扱い

重複コードは最初の一件だけが返る

完全一致のVLOOKUPと標準設定のXLOOKUPは、上から最初に一致した行の値を返します。INDEX＋MATCHも、この例のようにMATCHで完全一致の先頭位置を求める使い方では同じです。複数の単価を合計したり、最新の登録日を判断したりする働きはありません。

練習表では、=COUNTIF($F$2:$F$5,A2)でマスター内のP102の件数を確認でき、結果は1です。2以上なら、単価が同じでも登録の理由を調べます。COUNTIFの範囲も末尾まで含めなければ、追加した重複を見落とします。

同じコードが商品改定前後で使われている場合は、単純に片方を削除するより、適用日や規格を含めた識別方法が必要になることがあります。注文表に同じコードが何行もあるのは自然でも、マスターの一意性は別に考えます。削除前の確認には、Excelで重複データを見つけて消す｜条件付き書式・COUNTIF・重複の削除と消えない原因が参考になります。

近似一致は区間で決まる値に使う

近似一致は、見つからない商品コードの代用品を探す用途には向きません。使いどころは、点数の下限から評価を決めるような、区間に意味がある表です。この説明だけは商品表と別に、K1へ「下限点」、L1へ「評価」を置く例で考えます。

K2:K5へ順に0、60、80、90、L2:L5へ順にD、C、B、Aを入力します。下限が0からならD、60からならC、80からならB、90からならAという架空の基準です。数値の下限を昇順に並べ、点数75を調べます。

=VLOOKUP(75,$K$2:$L$5,2,TRUE)

結果はCです。75そのものがなくても、75以下の最大の下限である60の行を返します。「数値として距離が最も近い行」を選ぶわけではありません。昇順の条件を満たさない表では結果を信頼できず、完全一致に戻せばこの75は見つかりません。並べ替えるときはK列だけでなく、評価のL列も一緒に動かします。

下限未満の値や入力対象外の点数をどう扱うかは、検索式とは別に決めます。速くなることだけを理由に、商品コードの検索を近似一致へ変えることも避けます。入力値と区間のルールが明確な表で使う方法です。

IFNAとIFERRORは原因確認の後に付ける

#REF!は参照や列番号の問題を、#VALUE!は引数や値の扱いの問題を調べる手がかりです。VLOOKUPで三列の範囲に列番号4を指定すると#REF!、列番号0を指定すると#VALUE!になります。後者の条件は、MicrosoftのVLOOKUPの#VALUE!の説明でも確認できます。

IFNAは#N/Aを指定した表示へ置き換えます。利用環境で使える場合、VLOOKUPなら次のように書けます。C2では180を返し、C5へコピーすると要確認になります。

=IFNA(VLOOKUP(A2,$F$2:$H$5,3,FALSE),"要確認")

IFERRORは、#N/Aに加え#REF!や#VALUE!なども置き換えます。広く置き換える分、参照の破損まで同じ表示に隠れる点に注意します。IFNAでも元の単価欄にある#N/Aを隠す場合があるため、原因を確認した後で使います。両関数の対象は、Microsoftの論理関数一覧から確認できます。

大きな表の負担を減らし、結果を照合する

検索範囲と繰り返す計算を見直す

行が増えて重くなったら、まずF:Hのような列全体の参照が多数の式に使われていないか確認します。必要な行に範囲を絞ると計算の負担を減らせる場合があります。ただし狭くしすぎると未検索の行が生まれるため、最終行を管理する方法も一緒に用意します。

Excelの「テーブル」は、見出しと行を一つのまとまりとして扱う機能です。範囲をテーブルにすると、列名での参照や、行追加に合わせた参照の拡張を使いやすくなります。毎回商品が増えるマスターでは候補になりますが、追加した行がテーブル内に入ったかは確認します。

また、同じコードから商品名、単価などを別々に引く場合、毎回同じ検索を繰り返す構成になりやすくなります。慣れてきたら、MATCHで求めた位置を補助列に置き、複数のINDEXで共用する方法もあります。MicrosoftのExcelのパフォーマンス改善の資料にも、この考え方が紹介されています。

空白除去や型変換も、検索式の中で毎回大きな範囲へ掛けるより、取り込み時や補助列で一度整える方法を検討します。計算方法を手動へ切り替えるだけでは、古い結果を見てしまう原因になります。計算の設定を変えた場合は、更新を確認してから結果を使います。

エラーが消えた後も値を突き合わせる

練習表では、C2:C4が180、90、120で、D2:D4が540、180、600です。三件の金額の合計は1320になります。ただしP999の注文は未照合のままなので、1320を全注文の確定合計として扱ってはいけません。未照合の件数を残すことも検算の一部です。

実際の表では、先頭・中ほど・末尾に加え、追加したばかりのコード、単価0の商品、未登録コードを選び、元の行を目で探して照合します。合計だけ合わせると、複数の間違いが相殺される場合があります。検索したコード、商品名、単価の組み合わせを数件確かめる方が、列の取り違えを発見しやすくなります。

値の貼り付けは、その時点の計算結果を固定する操作です。後からマスターを直しても追随しなくなるので、固定する対象の期間、参照したマスター、未解決行の有無を確認します。コピーを残し、必要な範囲をコピーして「値」として貼り付けた後、選んだ数件と件数が一致しているか再確認します。

要確認を空欄や0へ置き換えてから固定すると、元々空欄だったのか、無料の単価なのか、未照合なのかを後で追いにくくなります。表示を整える前の状態を残し、確認を終えた行から確定する方が、修正箇所を説明しやすくなります。

まとめ

商品コードから単価を引く作業は、検索値、探す列、返す列を決めるところから始まります。VLOOKUPでは範囲内の列番号と完全一致を指定し、コピーするマスター範囲を$で固定します。XLOOKUPやINDEX＋MATCHを使う場合も、検索範囲と戻り範囲の行対応が基本になります。

見つからないときは、一件を選んで参照先と範囲を見直し、その後で型、空白、文字種を確認します。エラー表示を置き換えるのは原因を把握した後にし、重複や誤った列の値など、エラーにならない間違いも照合してください。

まずは練習表を作り、C2へ=VLOOKUP(A2,$F$2:$H$5,3,FALSE)を入力して、180になることを確かめてください。その式をC5までコピーし、90、120、#N/Aが順に出るところまで試すと、正常な検索と未登録の違いを自分の表で確認できます。