#VALUE! エラーは列番号の指定に誤りあり~VLOOKUP

#VALUE! エラーは列番号の指定に誤りあり~VLOOKUP
  • #VALUE! エラーの原因を知りたい
  • 解決方法を知りたい

VLOOKUPでの #VALUE! エラーは、列番号の指定に誤りがある場合に発生します。修正しない限りいつまでも出続けるので、放置しないようにしましょう。

この記事では、#VALUE! エラーの原因解決方法を解説します。

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

VLOOKUP を基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方|基礎から完璧マスター&便利技20選を超図解!

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~エラー値 #VALUE! を修正する

1. VLOOKUPの #VALUE! エラーの原因

VLOOKUPの#VALUE!エラーは、列番号の指定に誤りがある場合に発生します。

誤った指定の例は、次のようなケースです。

  • 列番号が1以上の数字になっていない
  • 列番号が数式の場合に計算結果が1以上の数値になっていない
  • 検索値の文字数が255文字以内になっていない

1-1. 列番号が1以上の数字になっていない

エラーの原因

VLOOKUPの列番号の指定が1より小さいと、#VALUE!エラーとなります。
必ず1以上の数字でなければなりません。

(参考)小数を含む場合は小数点以下切り捨てになる

エラーの原因

列番号の指定が小数を含む場合は、小数点以下は切り捨てになります。
切り捨て後の数字が1より小さくなると、#VALUE! エラーとなります。

1-2. 列番号が数式の場合に計算結果が1以上の数値になっていない

エクセルVLOOKUPの使い方【エラー解決編】COLUMNの列番号指定で失敗するケース
COLUMNの列番号指定で失敗するケース(画像はクリックで拡大)

≫ 解説図のPDFファイル

列番号を数式で指定している場合も、計算結果が1より小さい場合は#VALUE!エラーとなります。

列番号の指定でよく使われるのはCOLUMNです。(※)COLUMNは下記の(参考)を参照ください。
=COLUMN()」に加減算することで列番号を作り出します。このとき調整数値を間違えると#VALUE!エラーとなります。

(参考)列番号を数式で指定する方法

エクセルVLOOKUPの使い方【エラー解決編】COLUMNで列番号を指定する方法
COLUMNで列番号を指定する方法(画像はクリックで拡大)

≫ 解説図のPDFファイル

参考に、VLOOKUPの列番号をCOLUMNで指定する方法を解説します。

VLOOKUPは通常、数式をコピーしても列番号は変化しません。そのためコピーした後、列番号を手で書き換える必要がありました。
そこで横方向へのコピーで連動して変化するCOLUMN()を使う方法があります。

COLUMNの使用例

=COLUMN()
数式のあるセルの列番号が表示される

COLUMN()は、数式があるセルの列番号を表示します。数式がA列のセルにあるなら「1」が表示されます。(下図参照)

セルの列番号ABCDEFG・・・
=COLUMN()1234567・・・

このCOLUMN()を加減算して、VLOOKUPに必要な列番号を作り出します。VLOOKUPを右へコピーすると、列番号もあわせて変化することを利用します。列番号が一つずつ変化して欲しいケースであれば、一つの数式で足りるわけです。

=VLOOKUP($H2,$A:$F,COLUMN()-7,FALSE)

数式を右へコピーすると連動して列番号が変化するので、式を共通化することができます。

1-3. 検索値が255文字以内になっていない

エクセルVLOOKUPの使い方【エラー解決編】検索値が255文字を超えると#VALUE!エラーが発生
検索値が255文字を超えると#VALUE!エラーが発生(画像はクリックで拡大)

≫ 解説図のPDFファイル

検索値が255文字を超えると#VALUE!エラーが発生します。

上図では、検索値を1文字ずつ増やしてみました。255文字までは正常な値を返していますが、256文字でVALUE! エラーが発生しています。

(※)行数は関係ありません。255行を超えても大丈夫です。VLOOKUPは100万行まで検索できます。
詳しくはこちら
≫ エクセルの商品マスタ作成を自動転記で入力効率化&検索100万件でも楽々|

転職サイトは複数登録してベストな会社を見つけたい|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

2. VLOOKUPの #VALUE! エラーの解決方法

この章では、VLOOKUPの#VALUE!エラーの解決方法を解説します。

列番号を数字で直接書き込んでいる場合は、目で確認するだけです。1以上の数字になっているかチェックしましょう。
問題は数式で指定している場合です。少々テクニックが必要ですので、図を用いて解説します。

2-1. 数式の列番号以外の部分を削除してチェックする方法

エクセルVLOOKUPの使い方【エラー解決編】数式での列番号指定が正しくできているか確認する方法(1)
数式での列番号指定が正しくできているか確認する方法(1)(画像はクリックで拡大)

≫ 解説図のPDFファイル

(1) 数式を念のため丸ごとコピー

列番号のチェック方法

チェックしたい数式を念のため丸ごとコピーし、Wordやメモ帳などにコピーしておきます。

(2) 数式のあるセルを選択

列番号のチェック方法

数式の入っているセルを選択します。数式窓に数式が表示されます。

(3) 列番号の部分以外を削除

列番号のチェック方法

列番号の部分を残し、それ以外を削除します。
今回の例では」と「column()-7」を残し、それ以外を削除します。

(4) チェック完了なら「Ctrl+Z」で元に戻す

列番号のチェック方法

列番号が問題なく表示されたらチェック完了です。「Ctrl+Z」で元に戻しましょう。
今回の例では「2」が表示されます。

2-2. 数式の一部を切り出して別のセルに貼り付けてチェックする方法

エクセルVLOOKUPの使い方【エラー解決編】数式での列番号指定が正しくできているか確認する方法(2)
数式での列番号指定が正しくできているか確認する方法(2) (画像はクリックで拡大)

≫ 解説図のPDFファイル

(1) 数式のあるセルを選択

列番号のチェック方法

チェックしたい数式の入っているセルを選択します。
数式窓に数式が表示されるので、クリックします。
すると、検索値と検索範囲のセルが色分けして表示されます。

(2) 列番号を指定している部分だけをドラッグしてコピー

列番号のチェック方法

列番号を指定している部分だけをドラッグしてコピーします。
今回の例では「COLUMN()-7」です。
ここで「Esc」キーを押し、数式の選択状態を解除します。
検索値と検索範囲の色分けが消えたことを確認してください。

数式窓にあるを数式をさわっているときに、他のセルをクリックしてはいけません。そのセルが数式に組み込まれてしまう恐れがあります。

(3) 同じ列に貼り付けし先頭に「=」を追加

列番号のチェック方法

真下の空いているセルに貼り付けます。
そのままでは数式が文字列としてそのまま表示されてしまうので、先頭に「=」を追加します
想定通りの列番号が表示されているかチェックしましょう。
今回の例では「2」が表示されています。

必ず同じ列に貼り付けてください。COLUMNは列の番地によって値が異なります。真下以外の列に貼り付けると、異る結果を表示してしまいチェックになりません

スキルに自信がついたら新しい世界に踏み出そう!|転職サイト登録なび
転職活動で成功するGmailの作り方(1人で複数アドレス作成もOK)|転職サイト登録なび

まとめ|#VALUE! エラーが発生したら列番号を確認

この記事では、#VALUE! エラーの原因と解決方法を解説しました。

#VALUE! エラーは列番号の指定に誤りがある場合に発生します。データをむやみにいじらず、列番号の部分を中心にチェックしましょう。計算式で列番号を作り出している場合は、そのままでは確認が難しいので、列番号の部分だけ切り出すなどの工夫が必要です。

修正しない限りいつまでも出続けるので、放置しないようにしましょう。

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ 超図解!VLOOKUP|基礎から応用まで完璧マスター&便利技20選

エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。

エクセルの窓口検索

ブックマークお気に入りに登録していただけたら幸いです。

スキルに自信がついたら新しい世界へ飛び出そう!
転職サイト登録なび
転職サイト登録なび