shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

【Excel】VLOOKUP関数で同じ数字なのにエラーとなる原因と対応

ExcelのVLOOKUP関数で、検索値と同じ数字なのに#N/Aのエラーが発生することがあります。この主な原因は、セルの形式が一致していないことです。一方が文字列形式で、他方が数値形式である場合、エラーが発生します。また、半角と全角の違いもエラーの原因となります。

内容:

VLOOKUP関数でエラーの発生事例

例えば、次のようにVLOOKUP関数で数字を検索した事例では、#N/Aのエラーとなっているセルがあります。

この事例では、E列の5行目と6行目は数字が文字列形式、E列の7行目と8行目は全角数字で、B列の数字と異なるためにエラーが発生しています。

同じ数字なのにエラーが発生している

VLOOKUPのエラー例

検索値と数字があっているはずなのに、エラーが発生しているときは、セルの形式、半角全角の違いに原因がないかを確認します。

その他、エラーの発生や意図した結果と違うときは、次の点など数式内容が違う可能性が高いです。

  • 範囲等の指定が間違っている(範囲が相対参照の状態で数式をコピーし、範囲がずれている)
  • 検索方法で、TRUEを指定し、近似値検索になっている。

再度、数式をチェックします。

「セルの形式、半角全角の違い」が原因のエラー対応

「セルの形式、半角全角の違い」がVLOOKUPのエラー原因の場合、どちらかに統一する必要があります。

その主な方法と説明は次のとおりです。

元データの状態や、元データを変更して良いかで修正方法を選択する。

方法 補足
VALUE 関数を使用して、文字列を数値に変換(もしくは、TEXT関数で数値を文字列に変換) ○: 元データの変更不要。
×: 数字以外の文字列を含む場合、数式でIF文等の工夫が必要になる。
「データ」メニューの「区切り位置」を使用して、文字列か数値に変換 ○: 比較的容易。
×: 元データの変更が必要。
セル内に区切文字に該当する文字があると、列の分割が生じる。
数値の”0”をコピー後、[形式を選択して貼り付け] で各セルに乗算する  ○: 比較的容易。
×: 元データの変更が必要。
1を乗算するためセルの空欄は、”0”になる。
(文字列は、元の文字列を維持できる)
VALUE 関数を使用したエラー対応例:

以下は、VLOOKUP関数の検索値にVALUE関数の利用有無で、取得結果を違いを確認した事例です。

数字のセルの形式、半角全角の違いはVALUE関数でエラー解消できています。

ただし、VALUE 関数で文字列を変換するとエラーとなるため(下図のG10、G11)、数字と文字列が混在したデータの場合は、IF文等での工夫が必要になる。

VALUE関数で数値に変換し、数値の検索は適切にできている

VLOOKUP関数で検索値にVALUE関数の利用有無
参考情報:

support.microsoft.com

 

PowerQueryを利用した方法

VLOOKUP関数は簡易的な処理で利用することが多いと思います。

Excelの標準機能にあるPowerQuery(パワークエリ)を使用すると、元データは維持したままデータ変換、抽出等の自動化が可能です。

(一回限り程度の作業だと、VLOOKUP関数が手軽ですが)

shikumika.org

ExcelのVLOOKUP関数のように近似値で利用したい場合は以下にまとめています。

shikumika.org

 

以上、VLOOKUP関数で同じ数字なのにエラーが発生する原因とその対応策でした。