今回の記事ではVLOOKUP関数を使う際によく起こる、検索値とマスタの見た目は同じ数字なのにVLOOKUP関数で#N/Aエラーが出てしまい検索することができない際の対処法を解説したいと思います。
このようなことが起こる原因は同じ数字に見えてもエクセルで内部的に数値と文字列という違う表示形式になっていることが考えられます。
VALUE関数やCONCATENATE関数を使い、検索値とマスタの両方を数値や文字列に統一することによって対処することができます。
なぜ、検索できないかを覚えておくだけで簡単に対処することができますのでぜひ覚えておいていただければと思います。
↓VLOOKUP関数の様々なエラー対策はこちら↓
VLOOKUP関数がうまく表示されない時の8つの対処法とは？【エラー対策】
1．VLOOKUP関数で同じ数字なのに検索できない理由
上の表を見てください。
このVLOOKUP関数ではD2セルに入力した数字に対応した左側の商品コードに対応した商品名を返す基本的なVLOOKUP関数の計算式です。
しかし、よく見てみると「D2セル」には左の商品コードにもしっかりと存在する「2」という数字を指定しているにも関わらず、結果は「りんご」ではなく「#N/Aエラー」になってしまっています。
なぜこのようなことが起こってしまうかというと、同じ「2」という数字に見えてもエクセルの内部的に数字が数値として入力されているか、文字列として入力されているかという違いがあるからです。
エクセルの内部的には数値としての「2」と文字列としての「2」は別物なので、VLOOKUP関数のマスタに文字列の「2」は無いと判断されて「＃N/Aエラー」という結果が出てしまっています。
ここからはしばらく文字列と数値の変換方法の解説が続きます。
対処法をすぐに知りたい方はこちらを押してください。
それでは数値と文字列の違いとはどんなところでしょうか？
1-1．エクセルでの数値とは？
数値とは簡単に説明すると、計算に使える数字のことを指します。
エクセルでは数字を数値に指定していないと計算式に使うことができません。
表1をよく見ると、商品コードの数字は右側に揃えられていますがエクセルでは数値は右側に揃えて表示されます。
1-2．エクセルでの文字列とは？
文字列とは文字として並べられた数字のことを指します。文字列として入力した数字は計算式に使うことができません。
表1を見るとD2セルの数字は左に揃えられていますがエクセルでは文字列は左側に揃えて表示されます。
また、計算式が表示されているD4セルも文字列として入力することで結果ではなく計算式をそのまま表示させています。
上の表は左側が数値で右側が文字列で数字が入力されています。
「A1セル」と「A2セル」には1と2が入力されているので「1+2=3」と結果に3が表示されていますが、右側は「B1セル」と「B2セル」には文字列として1と2が入力されているだけなので計算には使用できず結果は0になってしまっています。
2．文字列を数値に変更する方法
それでは実際にVLOOKUP関数のマスタ側が文字列になっている場合に数値に変更する方法を解説したいと思います。
2-1．エラー表示から変換する
文字列として入力されているセルにはそのセルにエラーがあるという表示の左上に緑色の三角のマークがつきます。
そのセルを選択すると赤枠のような！（黄色いビックリマーク）が表示されます。
！（ビックリマーク）を押すとエラーに対応する項目が出てきます。
今回は数値に直したいので「数値に変換する」を選択します。
「B2セル」の文字列だった「1」が数値に変換されました。
それに伴い、計算結果も「1」が数値として認識されたことで「1」に変わりました。
2-2．VALUE関数を使用する
少ないデータでしたらエラーから1つずつ直しても問題ないですが、データの量が多いと1つずつ変換していくと相当な手間がかかってしまいます。
そんな時はVALUE関数を使うことで簡単に数値に変換することができます。
使い方もとても簡単で、例えば「B3セル」の「2」を数値に変換する場合、
=VALUE(B3)
と変換したいセルを指定するだけで数値に変換することができます。
計算式を入力したらオートフィルでコピーをすることで一度にたくさんの文字列を数値に変換することができます。
↓オートフィルについて詳しい解説はこちら↓
繰り返しの入力作業を一括で！オートフィルの使い方とは？
オートSUMの応用！平均やデータの個数をカウントする
2-3．コピーペーストで値として貼り付ける
最後にコピーペーストで数値に変換する方法を解説します。
こちらの変換方法もとても簡単で、数値に変換したい範囲をコピーしてペースト時に値として貼付けを選択するだけです。
ペースト時に右下に出るバインダーのようなアイコン（赤矢印）を選択し、値の貼付け（青矢印）を選択します。
この操作で、文字列を数値に変換することができます。
↓コピーペーストを更に便利に使いたい方はこちら↓
【小ワザ】意外と知らない？コピーペーストの種類
3．数値を文字列に変更する方法
ここまでは文字列を数値に変換する方法を解説してきましたがここでは逆に数値を文字列に変換する方法を解説したいと思います。
個人的には、VLOOKUP関数を使う場合は文字列を参照した方が間違いが少なくお勧めです。
3-1．表示形式で変更する
最初に解説するのは、セルの表示形式を文字列に変更する方法です。
文字列に変更したいセルを選択し、リボンメニューのホーム→数値→赤枠のウインドウを開き、その中の文字列を選択します。
数字が左寄りになりました。
これで文字列に変更されました。
3-2．’（アポストロフィー）を頭につける
数字の前にShift+7キーで’（アポストロフィー）を付けることで数値を文字列に変換することができます。
アポストロフィーを頭につけたことで「2」が文字列に変更され、VLOOKUP関数の結果も「りんご」と表示されるようになりました。
3-3．CONCATENATE関数を使う
CONCATENATE関数は通常セルとセルの文字をつなげるときに使用する関数ですが、結果を文字列で返すという特性を利用することで簡単に数値を文字列に変換することができます。
使い方もとても簡単で、
=CONCATENATE(変換したいセル)
と指定するだけです。
こちらも計算式を入力したらオートフィルでコピーをすることで一度にたくさんの文字列を数値に変換することができます。
↓オートフィルについて詳しい解説はこちら↓
繰り返しの入力作業を一括で！オートフィルの使い方とは？
オートSUMの応用！平均やデータの個数をカウントする
4．VLOOKUP関数で同じ数字なのに検索できない場合の対処法
それではいよいよ、VLOOKUP関数の結果がうまく反映されないときの対処法を解説したいと思います。
個人的には、VLOOKUP関数のマスタや検索値には文字列を使用することをおすすめします。
その理由は、頭に0が入力されている場合数値表示では頭の0の表示が消えてしまい見た目にも同じマスタを検索しているかわかりづらくなってしまうからです。
また、数値を文字列に置き換える際に3-3で解説したCONCATENATE関数を使うことをおすすめします。
この関数を使用することで、大量のデータがあっても一度に変更することができることと、VLOOKUP関数の結果を自動的に再計算させることができるからです。
また、数字だけのマスタにアルファベットを追加するなどの応用が利く点もおすすめする理由です。
↓頭の0の表示を使いこなしたい方はこちら↓
Excelで0の表示を使いこなしたい方はこちらからどうぞ！
それでは実際にVLOOKUP関数のエラーを解消してみましょう。
まずは、数値で入力されたマスタ側の参照値を空いているセルにコピーペーストします。
今回はA列からC列にコピペしました。
文字列に変換したいセルの一番上（今回はＡ2セル）を指定してCONCATENATE関数を入力します。
指定するセルは先ほどコピぺしたデータの一番上（今回はＣ2セル）です。
Enterを押したら、セルの右下にカーソルを合わせ、+（プラス）の表示になる部分でダブルクリックかそのまま表の一番下までドラッグし、オートフィルですべてのデータにCONCATENATE関数を反映させます。
全てのデータが文字列に変換され、VLOOKUP関数の結果もエラーから「りんご」に変更されました。
最後に、コピペした先のデータを消せば作業は完了です。
↓このページで解決しなかった場合はこちらもお読みください↓
VLOOKUP関数がうまく表示されない時の8つの対処法とは？【エラー対策】
今回は、VLOOKUP関数で同じ数字なのにうまく検索できないときの理由と対処法、数値と文字列の変更時方法について解説しました。
この記事ではVLOOKUP関数を例に解説しましたが、同じように使うことができるINDEX、MATCH関数でも今回と同じ対処をすることでエラーを解消することができます。
Excelの内部的には数値と文字列は同じ見た目でも違うということを、ぜひ覚えておいていただければと思います。
最後までお読みいただきありがとうございました。
