ExcelのVLOOKUPで同じ値があるのに#N/Aになるとき

カテゴリ：Excel

「商品コードは一覧にあるのに、VLOOKUPの結果が#N/Aになる」。

見比べても同じなら、数字の保存のされ方や、前後に入った空白が原因かもしれません。Excelでは、数値の101と、文字列として保存された101が一致しないことがあります。

今ある表を使って、コードを別の列で整えてみましょう。今回は、大文字・小文字を区別しない、半角英数字の商品コードを対象にします。

1．元ファイルを残し、検索する場所を確認する

先にファイルを複製し、コピーを開きます。

商品コードで探す式は、最後をFALSE（完全一致）にします。また、コードの列が指定範囲の一番左にあり、探す行が範囲に含まれているか確認してください。ここを直して商品名が出れば、次の作業は不要です。

2．空いている列でコードをそろえる

ここでは、一覧のコードがA列、商品名がB列、探すコードがD2、一覧が2〜100行目にある表として説明します。ご自分の表に合わせて、式の列・セル・最終行を読み替えてください。

まず、隣り合う空き2列を使います。G列とH列が空いていれば、G2に次の式を入れてEnterを押します。

=TRIM(A2&"")

G2をコピーして、G3からG100まで貼り付けます。続いてH2に「=B2」を入力し、H3からH100までコピーします。G列に整えたコード、H列に商品名が並びます。

「&""」で文字列にそろえ、TRIMで前後の半角スペースを取り除いています。セルの表示形式を「文字列」に変えるだけでは、入力済みの数値は変換されません。元のA列・B列はそのまま残せます。

G列・H列にデータがある場合は、別の隣り合う空き2列を選び、次の式の検索範囲もその2列に変えてください。

3．整えたコードで、もう一度探す

D2に探すコードを入力してから、元の一覧・D2・補助列とは別の空きセルに、次の式を入れます。

=VLOOKUP(TRIM(D2&""),$G$2:$H$100,2,FALSE)

探す側のD2も、同じ方法で整えています。たとえば一覧に文字列の101と商品名「ノート」があり、D2が数値の101なら、結果は「ノート」です。

整えたコードが複数の行で重複したら、いったん止めて確認します。VLOOKUPは最初の一致を返すため、エラーが消えても別の商品を拾うおそれがあります。直した後は数件を元の一覧と見比べましょう。

4．直らないときに、無理に変えないもの

TRIMだけでは全角スペースや、Webから入り込む改行しない空白（NBSP）は取れません。混入した文字を確認せずに、空白を一括削除しないでください。また、途中の連続した半角スペースも1個になります。空白に意味があるコードには、そのまま使わないでください。

「00101」と「101」は別のコードとして残します。先頭0のあるコードを手入力するときは、一覧側・探す側とも「'00101」のように半角の「'」を付けると、文字列として入力できます。

表示形式だけで付けた0は、この式には引き継がれません。数値化で消えた先頭0や、16桁以上の数値で失われた桁は、この式では元に戻せません。元のコードを確認してください。

XLOOKUPに変えるだけでも、数値と文字列の違いや余分な空白は直りません。まず、探す側と一覧側のコードをそろえることが大切です。

やり直すときは作業用コピーを閉じ、残しておいた元ファイルから再開できます。

ご自分の表では直し方に迷う場合は、下のプロフィールのExcel・CSVサービスから「VLOOKUPで見つからない」と初回のご相談をお送りください。

https://coconala.com/users/1867172