【Excel】VLOOKUPの#N/Aが型不一致のとき:数値・文字列を確認してそろえる方法

【Excel】VLOOKUPの#N/Aが型不一致のとき:数値・文字列を確認してそろえる方法
🛡️ 超解決

VLOOKUPで「101」を探しているのに#N/Aになる場合、検索値が数値の101、マスターが文字列の「101」という違いが原因になることがあります。見た目や左右の配置だけでは判断せず、ISNUMBERとISTEXTで両方の型を確かめましょう。

ただし、#N/Aがすべて型の不一致というわけではありません。検索する列や完全一致の指定も確認が必要です。この記事では、照合用の値を数値にそろえる場合と、先頭0を持つコードとして文字列にそろえる場合を分け、原本を壊さず修正する手順を説明します。

ADVERTISEMENT

1. 完全一致の設定と検索する列を確認する

A2を検索値、D2:D4をマスターのコード、E2:E4を品名とする例で考えます。B2の式は次のとおりです。第4引数のFALSEは完全一致を指定しています。

=VLOOKUP(A2,$D$2:$E$4,2,FALSE)

VLOOKUPが探すのは指定範囲の左端、この例ではD列です。E列に検索したいコードがあるのにD:Eを指定した場合は、型を変えても目的の検索にはなりません。最初に範囲と列の順序を確認し、次にD列に該当するコードが存在するかを見ます。

第4引数を省略すると近似一致として動作するため、IDや商品コードの照合ではFALSEを明記します。並べ替えによって偶然結果が出た状態を修正完了とせず、まず検索の条件を固定しましょう。

練習ではA2に数値101、D2に先頭のアポストロフィ付きで'101、E2に「ノート」を入力します。アポストロフィは文字列として入力するための印で、通常セルの表示は101です。見た目が同じでも、完全一致の照合で一致しない例を作れます。

※ お探しの解決策が見つからない場合は、こちらの「Excelトラブル完全解決データベース」で他のエラー原因や解決策をチェックしてみてください。

2. ISNUMBERとISTEXTで両側を調べる

空いている作業用セルに次の式を入れ、検索値A2と、合致するはずのマスターD2の型を比べます。

=ISNUMBER(A2)
=ISTEXT(A2)
=ISNUMBER(D2)
=ISTEXT(D2)

練習例ならA2はTRUE・FALSE、D2はFALSE・TRUEになります。ここで初めて、数値と文字列の違いが確認できます。左寄せ・右寄せは手動で変更できるので、型の証拠にはなりません。緑の三角印も数値が文字列になっている場合だけの専用表示ではなく、印がないことも正常な型の保証にはなりません。

型が同じなら、余分な空白、全角・半角、似た別の記号、検索範囲の漏れを調べます。LENで長さを比較し、必要ならUNICODEで疑わしい文字を調べます。数値の101と文字列の101をそろえる問題と、文字列「101 」の空白を除く問題は別です。

列の途中だけ型が違うケースもあるため、一件が直ったら完了とはせず、同じ診断式を列全体へ展開します。外部データの貼り付けや手入力が混在している場合は、どの行から変わっているかも確認すると原因を追いやすくなります。

3. 数値へそろえる場合は作業列でVALUEを使う

検索キーが計算用の数値で、先頭0や桁の表記を区別しない場合は、VALUEで数値化できます。検索値A2が文字列、マスターD列が数値なら、C2に=VALUE(A2)を入れ、C2のISNUMBERがTRUEになったことを確認します。

=VLOOKUP(C2,$D$2:$E$4,2,FALSE)

逆にマスターD列が文字列なら、検索値だけを数値にしても直りません。たとえばG2に=VALUE(D2)、H2に=E2を入れて下へコピーし、数値化したコードを左端に置いたG:Hの作業用マスターを作ります。

=VLOOKUP(A2,$G$2:$H$4,2,FALSE)

これで検索値とマスターを数値で合わせられます。検索値も文字列ならVALUEで整えた作業列を検索値にします。どちらを変換したか分からなくならないよう、作業列の見出しに「数値化コード」などと記録してください。

VALUEで#VALUE!になったセルは、Excelが数値として解釈できない文字列です。単位、余計な空白、地域による小数点・桁区切りの違いなどを調べます。=A2*1でも数値化できる場合はありますが、先頭0を失う点は同じなので、文字列コードの一括修正には使いません。

数値として扱ってよい列なら、警告メニューの[数値に変換]や[データ]の[区切り位置]も選択肢です。ただし元列を書き換える操作になるため、まず列を複製し、プレビューと列のデータ形式を確認してから確定します。型を見ずに列全体へ実行しないでください。

4. 先頭0のあるコードは文字列にそろえる

商品コード「001」と「1」を別物として扱うなら、数値化は不適切です。マスターが三桁の文字列コードと決まっていて、検索側だけが数値1になっている場合は、TEXTで三桁の文字列を作れます。

=TEXT(A2,"000")

A2が数値1なら結果は文字列「001」です。これをC2に置き、文字列コードを持つD:Eへ=VLOOKUP(C2,$D$2:$E$4,2,FALSE)で照合します。型を確認するならISTEXT(C2)がTRUEになることも見ます。

数値のマスターには文字列101をVALUEで数値101にする。三桁文字列コードのマスターには数値1をTEXTの000書式で文字列001にする。
変換する方向はマスターの仕様に合わせます。先頭0が意味を持つコードを、数値へ一括変換しないでください。

「000」はこの例の三桁という仕様に合わせた指定です。実際には五桁や英字付きなど別の形式もあるので、勝手に三桁へそろえないでください。数値1234へこの形式を使っても、三桁に切り捨てるわけではありません。桁数が固定という前提を確認してから適用します。

元のコードが001だったのか0001だったのか不明なら、数値1から正解は推定できません。原本を取り直す必要があります。またExcelの数値の精度は15桁なので、16桁以上の識別番号を数値として取り込んで失われた桁は、後からTEXTで復元できません。長い番号は取り込み時点から文字列で保持します。

5. 表示形式の変更だけで直ったと判断しない

既に入力された数値の表示形式を[文字列]へ変えただけでは、内容が文字列へ変換されたとは限りません。同様に、文字列の数字を[標準]に変えただけでも、数値へ変わらないことがあります。見た目の書式変更と値の型の変換を分けて確認してください。

また、数値1に表示形式「000」を設定して001と見せても、中身は数値1です。TEXTで作る文字列「001」とは異なります。VLOOKUPの型をそろえる目的なら、表示だけでなくISNUMBER・ISTEXTの結果も確認します。

数値を文字列にしたいときにVALUEを使うのは逆方向です。VALUEは文字列から数値へ、TEXTは数値を書式付き文字列へ変えます。練習表で一度方向を確認し、実データでは変換前と変換後を別列にして適用しましょう。

F2とEnterによる再入力や区切り位置の再解釈も、セルの形式や内容の影響を受けます。全件に同じ操作を繰り返す前に、先頭0・長い番号・文字混在の代表行で結果を確かめます。原本を書き換えず作業列で変換する方法なら、誤った方向にそろえても式を直してやり直せます。

6. 一致件数と返った内容を検品して確定する

修正後は#N/Aの残る件数を数え、数値と文字列が混在していた行だけでなく、もともと正常だった行も確認します。コードに対応する品名が正しいかを原資料と照合してください。コードが重複していると、VLOOKUPは最初の一致を返すため、結果が出ただけでは目的の行を取れたとは限りません。

一致しないものをIFERRORで空欄にするのは、原因調査の代わりにはなりません。新規コードでマスター未登録なのか、入力間違いなのか、型以外の相違なのかを分けます。必要なら「未登録」「型確認」などの確認用列を作り、修正対象を残します。

確認が済んだ作業列を正式データへ採用する場合も、元列と変換ルールを保存します。誤って上書きした直後は元に戻し、後からは原本から再処理します。先頭0の意味とマスター側の型を確認してから変換方向を選ぶことが、型不一致を直しながら正しいコードを守る基本です。

📊
Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。
この記事の監修者
📈

超解決 Excel・Word研究班

企業のDX支援や業務効率化を専門とする技術者チーム。20年以上のExcel・Word運用改善実績に基づき、不具合の根本原因と最短の解決策を監修しています。ExcelとWordを使った「やりたいこと」「困っていること」「より便利な使い方」をクライアントの視点で丁寧に提供します。

🏆
超解決 Excel検定 あなたのExcel実務能力を3分で測定!【1級・2級・3級】