参照データに誤記があってもVLOOKUP関数で値を参照する方法【ワイルドカードの使い方】

参照データに誤記があってもVLOOKUP関数で値を参照する方法【ワイルドカードの使い方】

参照データの誤記

ビジネスで頻繁に用いられるExcelの関数と言えばVLOOKUP関数です。

ある元データから特定の値を参照することが可能で便利な関数です。

しかし関数にも限界があり、参照データに誤記があれば当然値の参照はできません。

こうした場合、参照データの誤記修正ができるなら良いのですが、場合によっては参照元は編集できないこともあります。

そこで今回は参照データを編集せずに誤記を考慮した関数の設定を紹介します。

VLOOKUP関数の基本的な使い方はこちらで紹介しています。

VLOOKUP関数の設定

データの例

まずは参照データとして下図のような部品データを例とします。

こちらのデータは編集できないものとします。

こちらの部品データの「品目番号」をキーとして「棚番」を表示させる関数を作成します。

つまり、下図のようなリストを完成させることが今回の目的です。

VLOOKUP関数を活用し、「品目番号」を検索値、参照データのB列からD列が検索範囲、列番号は3で検索方法はFALSE(完全一致)とした場合、次のような関数になります。

=VLOOKUP(A3,部品データ!$B$3:$D$10,3,FALSE)

これで品番「0000-125A」の棚番は正しく「A1」と表示されました。

続いてオートフィルで下2つの品番の棚番を表示させてみましょう。

「#N/A」のエラーが表示されてしまいました。

原因は参照データの誤記です。

今回参照した「0000-130K」および「0000-129G」の部品データをよく見てみましょう。

「0000-130K」は「0000_130K」のようにハイフンアンダーバーに、

「0000-129G」は「0000ー129G」のようにハイフンが半角から全角に記載されていました。

このような記号の不統一による参照不可がエラーを発生させていました。

本来であれば記号の誤記修正を行いたいところ。

しかし前述の通り、参照元である部品データは編集できません。

ワイルドカードを活用する

記号の不統一はよくある誤記です。

検索値の一部を無視し、信頼できる範囲だけで検索することで誤記を気にせずVLOOKUP関数を使用できます。

このために必要となるのがワイルドカードの「*」(アスタリスク)です。

ダブルクオーテーション「”」で挟んで使用します。

検索値の部分を変更し、前半の「0000-」は無視、後半の「125A」などの部分のみをキーとして関数を修正します。

=VLOOKUP("*"&RIGHT(A3,4),部品データ!$B$3:$D$10,3,FALSE)

Right関数は文字列の右端から指定の文字数を抽出する関数です。

この関数でオートフィルを使用すると以下のようになります。

正しい棚番が入力されました。

後半の信頼できる部分のみをキーとしたことで誤記を無視することができました。

よくある間違った事例

VLOOKUP関数の間違った使用例として、検索方法の「TRUE」と「FALSE」を誤ったまま使用している例を見かけます。

TRUEは「近似一致」、FALSEは「完全一致」です。

ハイフンの半角や全角の違いも「近似一致」であると勘違いしてTRUEを使ってしまうと以下のように失敗します。

=VLOOKUP(A3,部品データ!$B$3:$D$10,3,TRUE)

棚番が表示されていますが、全く別の棚番です。

TRUEはあくまでも「数値的な」近似一致の機能です。

例えば「10から20の間」のような、数値の前後を含む使い方をします。

よって、今回のような数値の上も下もない品番をキーとした場合では上手く表示できません。

さらに、TRUEとFALSEを間違えて設定した場合、エラーにならないというのが大きな問題です。

正しく表示されていると思い込んでしまうと痛い目にあいます。

まとめ

参照データに誤記があってもVLOOKUP関数で値を参照する方法を紹介しました。

  • ハイフンなどの記号の不統一による誤記に注意する
  • 誤記を無視するにはワイルドカードを活用する
  • 完全一致と近似一致を勘違いして使用しないこと

当サイトでは各種PCスキルを公開しています。

(Visited 493 times, 1 visits today)

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

6 + six =