ExcelのVLOOKUPとXLOOKUP｜別の表から値を引く書き方と、#N/Aになる原因の直し方
#N/Aは一件を選んで原因を切り分ける
データを直す前に数式を読む
#N/Aは、検索で一致する値を見つけられないときによく出るエラーです。ただし、検索先のセルにすでに#N/Aが入っていて、それを返している場合もあります。まず検索に失敗したのか、見つかった行の単価がエラーなのかを確認します。
一部の行だけ失敗するなら、そのうち一件を選びます。全列を数値へ変換したり、一括置換したりする前に、次の順で見ます。練習表のC5なら、検索値はA5のP999です。
- 数式バーで、検索値が同じ行のA5になっているか確認します。
- 検索する場所がマスターのコード列になっているか見ます。
- 範囲の開始行と終了行を確認し、目的のコードが範囲内にあるか探します。
- VLOOKUPなら第4引数が
FALSEか0か確かめます。 - 候補のコードがある場合だけ、両方の保存形式や文字を比較します。
- 一致した行があるなら、その行の単価欄自体にエラーがないか確認します。
P999はマスターにないため、この例では未登録が原因です。数式を複雑にしても単価は決まりません。入力ミスなのか、登録漏れなのかを商品情報の管理元へ確認します。似たコードの単価を埋めたり、エラーを消すために適当な行を追加したりしないようにします。
最終行の不足と列番号の間違い
商品を6行目へ追加しても、固定範囲$F$2:$H$5には含まれません。絶対参照はコピー時のずれを防ぎますが、表の末尾へ追加した行を必ず取り込む指定ではありません。追加後は最終行まで範囲に含めるか、後述するExcelのテーブルを検討します。
列番号の間違いは、必ずしも#N/Aにはなりません。練習表で列番号2を指定すると、P102は見つかり、商品名のボールペンが返ります。文字が返っているなら検索自体は成功しているので、検索キーを変換する前に、返す列を見直します。
第4引数の省略も同様です。値が出ていることは完全一致の証拠になりません。#N/Aの行だけを直すと近似一致による誤った値が残るため、同じ列の式全体で一致の指定がそろっているか確認します。
数値と文字列は見た目だけでは分からない
練習表のP102は、英字を含む文字列です。ところが実際のコードが102のような数字だけだと、数値の102と、文字列として保存した102が混ざりやすくなります。見た目が同じでも、検索では一致しない原因になります。Microsoftの#N/Aの修正案内も、検索値と元データの型の違いを確認点に挙げています。
=ISNUMBER(A2)と=ISNUMBER(F3)で、それぞれが数値か確認できます。練習表ではどちらも文字列のP102なので、結果は両方FALSEです。数字だけのコードで片方がTRUE、もう片方がFALSEなら、型がそろっていない手がかりになります。
ただし、両方FALSEだから同じ文字列とは限りません。空白や文字種の違いも残ります。数値なら通常は右寄せ、文字列なら左寄せという見分け方もありますが、配置を手動で変えられるため、寄せ方だけで決めないでください。
空白・全角半角・文字列の数値を確認する
LENとTRIMで余分な文字を調べる
練習表のP102は四文字です。空きセルで次の式を計算すると、どちらも4になります。末尾の半角空白が一つ混ざると、見た目では気づきにくくても文字数は5になります。
=LEN(A2)
=LEN(F3)文字数が違う場合は、数式バーで先頭と末尾を確認します。文字数が同じでも、全角の英数字や別の記号が入っていれば一致するとは限りません。LENは違いを探す手がかりであり、文字列が等しいことの証明ではありません。
前後の余分な空白にはTRIMを使えます。I1に「注文コード確認」と見出しを付け、I2へ次の式を入れると、元のA列を残したまま結果を見られます。
=TRIM(A2)TRIMは前後の空白を除き、途中に連続する空白は一つにします。コードの内部の空白が意味を持つ場合は、この変化も確認します。また、Webからコピーした文字に含まれる特殊な空白のように、TRIMでは除けないものもあります。TRIMのあとでLENの結果が変わらないのに一致しないときは、こうした文字を疑います。詳しくはMicrosoftのTRIM関数の説明で確認できます。
一方だけ整えても検索先は変わらない
検索値がきれいでも、マスター側のP102に空白が付いていれば一致しません。両方を調べる場合は、J1に「マスターコード確認」と見出しを付け、J2へ=TRIM(F2)を入力してJ5までコピーします。I2の注文コードと、J2:J5の整えたマスターコードを照合する例は次のとおりです。
=XLOOKUP(I2,$J$2:$J$5,$H$2:$H$5,"要確認")XLOOKUPが使えなければ、同じ対応をINDEX＋MATCHで指定できます。
=INDEX($H$2:$H$5,MATCH(I2,$J$2:$J$5,0))練習表では空白がないため、どちらも180を返します。補助列の確認後も、マスターのJ列だけを単独で並べ替えないでください。検索用コードとH列の単価の行対応が崩れると、別の商品に一致したように見える原因になります。
VALUEは数値にしてよいデータだけに使う
文字列の数値を数値へ変換する関数がVALUEです。例えば=VALUE("102")は数値の102を返します。しかし練習表の=VALUE(A2)は、P102を数値にできないため#VALUE!になります。英字入りのコードにVALUEを掛けてエラーを直そうとする方法は合いません。
数字だけのコードでも、00102と102を区別する運用なら、数値化すると区別が失われます。「計算に使う数量や単価」なのか、「桁数を保つ識別用コード」なのかで変換方針を決めます。表示形式を文字列へ変更するだけでは、すでに保存された値の型が変わらない場合もあります。Excelで日付や数字の表示がおかしいとき｜####・シリアル値・指数表示・先頭のゼロの原因と直し方も合わせて確認してください。
全角のＰ１０２と半角のP102が混ざる場合は、登録ルールに沿ってそろえます。コードに意味のある文字を消さないよう、元の値と変換後の値を補助列で並べ、重複が生まれないかも見ます。単に一致件数が増えたことだけで、変換が正しいとは判断できません。
CSVから毎回同じ崩れが入るなら、検索式へ処理を足し続けるより、取り込み時の列の型を見直します。CSVの文字化けの直し方｜Excelの取り込みからゼロ落ち・保存後の確認まででは、文字コードとコード列の保存方法を分けて確認できます。
