【Excel】エクセルのVLOOKUPで文字列を検索する方法（数値との違い・一致しない）

いつも記事を読んでいただきありがとうございます！！！ これからもお役に立てる各情報を発信していきますので、今後ともよろしくお願いします(^^)/

ExcelのVLOOKUP関数で商品コードや社員番号を検索したとき、見た目は同じ文字なのに一致しないことがあります。

とくに、数値として入力された値と文字列として保存された値が混在すると、検索結果が#N/Aエラーになりやすいため注意が必要です。

この記事では、VLOOKUPで文字列を検索する数式、数値との違い、一致しない場合の確認方法を順番に解説します。

 

VLOOKUPで文字列を完全一致検索する方法

検索値	商品コード	商品名	単価
A2	001	ノート	120
検索対象	002	ペン	150
検索対象	003	消しゴム	80

それではまず、文字列のコードを完全一致で取得する基本の数式について解説していきます。

VLOOKUPでは、検索したい文字列を左端の列に置き、戻したい列番号を指定する構成が基本です。

 

検索値と検索範囲の指定

VLOOKUPの第1引数には、検索する文字列が入ったセルを指定します。

たとえばA2に001と入力し、B2からD4に一覧表がある場合は、A2と$B$2:$D$4を数式に設定します。

検索範囲はコピーしてもずれないように、絶対参照の$記号を付ける方法が実務では便利です。

先頭の列で検索するため、商品コードがC列にあり商品名がB列にあるような表では、そのままの並びでは検索できません。

検索用のコード列を表の左側へ移動するか、後述する別の関数を検討しましょう。

 

完全一致を指定する第4引数

文字列の検索では、VLOOKUPの第4引数をFALSEに指定することが重要です。

FALSEは完全一致検索を意味し、001と001だけを同じ値として扱います。

第4引数を省略すると近似一致になるため、文字列コードの照合には向きません。

品番、郵便番号、会員ID、社員コードのように一文字でも違えば別データとなる項目では、FALSEを必ず付けましょう。

 

検索結果を下方向へコピーする操作

数式を入力したセルの右下にある小さな四角をドラッグすると、下の行へ数式をコピーできます。

検索値がA2、A3、A4と並ぶ場合、VLOOKUPの第1引数だけが各行に合わせて変化します。

検索範囲を絶対参照にしていれば、参照表は固定されたままです。

入力後は、検索結果のセルを選択し、数式バーで参照先が意図どおりか確認しましょう。

 

文字列と数値で発生する検索結果の違い

表示内容	実際の形式	VLOOKUPでの判定
001	文字列	001として検索
1	数値	1として検索
001	数値の表示形式	1として検索

続いては、見た目が似ていても検索結果が変わる文字列と数値の違いを確認していきます。

Excelではセルに001と表示されていても、実体が文字列か数値かによってVLOOKUPの一致判定が変わります。

 

先頭のゼロを含むコードの扱い

商品コード001、支店番号010、郵便番号などは、先頭のゼロ自体に意味があります。

このような値を数値として入力すると001は1になり、表側の001という文字列と一致しないことがあります。

文字列として入力するには、セルの表示形式を文字列に変更してから入力するか、入力時に先頭へアポストロフィを付けます。

アポストロフィはセルには表示されず、001を文字列として保存するための入力記号です。

コード列は最初から文字列形式で統一することが、後からの修正を減らす近道になります。

 

表示形式だけを変更した場合

数値の1に000というユーザー定義の表示形式を設定すると、画面上では001と表示されます。

しかし、セルの実体は数値の1のままであり、文字列の001とは同じ値ではありません。

これは帳票の見た目を整える用途には便利ですが、コード照合では混乱の原因になります。

検索値だけをTEXT関数で変換する場合は、検索表側も同じ桁数の文字列で管理されているか確認しましょう。

 

数値と文字列を確認する関数

セルの形式を調べるには、ISTEXT関数とISNUMBER関数が役立ちます。

=ISTEXT(A2)の結果がTRUEならA2は文字列であり、=ISNUMBER(A2)がTRUEなら数値です。

また、セル左上の緑色の三角形や、セル内で左寄せになる表示も確認材料になります。

ただし、配置設定で左寄せや右寄せを変更している場合もあるため、関数で調べる方法が確実です。

 

文字列検索で一致しない原因の確認

検索値	表の値	考えられる原因
ABC-01	ABC-01	末尾スペース
001	1	データ型の違い
東京	東京都	文字数の違い

続いては、VLOOKUPで文字列が一致しない代表的な原因を確認していきます。

数式が正しく見えても、空白文字、全角半角、改行、データ型の差によって検索できないことがあります。

 

余分なスペースと改行の削除

コピーしたデータには、先頭や末尾に半角スペース、全角スペース、改行が入っていることがあります。

たとえばABC-01とABC-01の後ろにスペースがある値は、Excelでは別の文字列です。

全角スペースも混ざる場合は、SUBSTITUTE関数で全角スペースを空文字へ置換してからTRIM関数を使いましょう。

このように補助列で文字列を整形し、その補助列をVLOOKUPの検索列にすると原因を切り分けやすくなります。

 

全角半角と似た文字の違い

半角のAと全角のＡ、半角ハイフンと長音符は、見た目が似ていても別の文字です。

カタカナ、数字、記号を含む型番では、入力元ごとに表記ゆれがないか確認しましょう。

=ASC(A2)は全角英数字を半角へ変換する際に利用できます。

同じように見える文字ほど検索エラーの原因になりやすいため、数式バーで一文字ずつ確認する姿勢が大切です。

 

検索範囲と列番号の見直し

VLOOKUPは検索範囲の左端だけを検索するため、範囲の開始位置がずれていると値が見つかりません。

また、第3引数はシート全体の列番号ではなく、指定範囲内で左から数えた列番号です。

たとえばB列からD列を選んだ場合、D列は3列目になります。

検索列、検索範囲、戻り列、第4引数を順に確認すると、多くのVLOOKUPエラーを解消できます。

 

文字列へ変換して検索する数式

検索用の値	変換方法	利用場面
1	TEXT関数	ゼロ埋めコード
001	VALUE関数	数値一覧の検索
ＡＢＣ	ASC関数	全角英数字の統一

続いては、データ型がそろえられない場合に文字列へ変換して検索する数式を確認していきます。

元データを直接修正できない場合でも、補助列や数式内の変換によって検索精度を高められます。

 

TEXT関数で桁数をそろえる方法

数値の1を文字列の001として検索したい場合は、TEXT関数で桁数を指定します。

コードの桁数が4桁なら、書式文字列を0000に変更します。

検索表のコード桁数とTEXT関数の桁数を一致させることが必要です。

 

文字列を数値に変換する方法

検索表が数値で統一されている場合は、検索値の文字列を数値へ変換する方法もあります。

=VALUE(A2)は、数字だけで構成された文字列を数値に変換します。

数式は=VLOOKUP(VALUE(A2),$D$2:$F$100,2,FALSE)のように記述できます。

ただし、001を1に変換すると先頭のゼロという情報は失われます。

コードとしてゼロが意味を持つ場合は、数値化より文字列化を選ぶほうが安全でしょう。

 

補助列を使ったデータ整形

複雑な変換を数式内に詰め込むより、一覧表の横に補助列を作る方法は管理しやすい方法です。

補助列には、TRIM、CLEAN、SUBSTITUTE、ASC、TEXTなどを組み合わせた整形済みの値を表示します。

VLOOKUPでは整形済みの補助列を含む範囲を指定します。

データを整える処理と検索する処理を分けることで、後からエラー原因を追いやすくなります。

 

VLOOKUPのエラー表示と対処の考え方

表示	主な原因	確認内容
#N/A	検索値がない	文字列の差
#REF!	列番号が範囲外	第3引数
#VALUE!	引数の指定ミス	範囲と型

続いては、VLOOKUPで表示されるエラーの意味と、文字列検索での対処を確認していきます。

エラーを単に非表示にするのではなく、何が一致していないのかを把握することが大切です。

 

#N/Aが表示される場合

#N/Aは、検索値に対応するデータが検索範囲に見つからない場合に表示されます。

本当に登録がない場合だけでなく、数値と文字列、余分なスペース、表記ゆれでも発生します。

=COUNTIF(B:B,A2)を使うと、指定した値がB列に何件あるか確認できます。

件数が0なら、VLOOKUPの前に検索値そのものを見直しましょう。

 

IFERROR関数で見やすく表示する方法

検索対象がないことを想定する一覧では、IFERROR関数でエラー表示を任意の文言に変えられます。

ただし、数式ミスまで該当なしとして隠れる可能性があります。

作成直後は通常のVLOOKUPで動作を確認し、問題がないことを確認してからIFERRORを追加すると安心です。

 

検索表の重複データへの注意

VLOOKUPは同じ検索値が複数ある場合、検索範囲の上から最初に見つけた行だけを返します。

たとえば同じ商品コードに異なる商品名が登録されていても、後の行は検索結果に反映されません。

重複を確認するには、条件付き書式やCOUNTIF関数を使います。

文字列の一致だけでなく、検索キーが一意であることも正確な表作成には欠かせません。

 

まとめ エクセルのVLOOKUPで文字列を検索する方法（一致しない・数値との違い）

確認項目	重要な考え方
検索方法	第4引数はFALSE
データ型	検索値と表側を統一
不一致	空白と表記ゆれを確認

エクセルのVLOOKUPで文字列を検索する際は、検索値と検索範囲のデータ型をそろえ、完全一致のFALSEを指定することが基本です。

001のようなコードは、数値の1とは異なる扱いになるため、先頭ゼロを含む値は文字列として管理しましょう。

一致しない場合は、スペース、改行、全角半角、検索範囲、列番号を順に確認すると原因を見つけやすくなります。

TEXT関数やVALUE関数を使えば、元データの事情に合わせて検索用の値を変換できます。

補助列でデータを整形してからVLOOKUPを使う方法も、実務で安定した結果につながります。

表記ルールを最初に決め、商品コードやIDを一貫した形式で入力していきましょう。