VLOOKUP関数が思うように使えない場合

VLOOKUP関数のエラーは確認が面倒・・

VLOOKUP関数は、Excelの中では良く使われる関数の1つだと思いますが、SUM関数やIF関数などと比べると少し敷居が高いかもしれません。

SUMやIFでは基本的には、1つ(あるいは複数)のセルを「点と点、あるいは直線的に扱う」のに対して、VLOOKUP関数の場合は、「縦と横の動きが加わり、表全体が対象範囲」となります。

「このような動きだからこそ」と思いますが、設定する引数も4個と割と多い方だと思います。
この便利なVLOOKUP関数は、その守備範囲と引数の多さ故なのか、エラーになるとその原因究明と解決が面倒なケースがよくあります。

そこで、今回はVLOOKUP関数を使っていて、エラーなどで「期待している結果にならなかった」とき、どの辺りを確認すべきかなどについて説明します。

※当ページは以前に公開した内容を大刷新しました(現在の知見を含めました)。

VLOOKUP関数は直角的検索ができる
VLOOKUP関数は立体的に動く

VLOOKUP関数が思うように使えない場合にするべきこと

当ページのタイトルでもありますが、「VLOOKUP関数で思うように使えない、思うようにいかない」状況とは、エラーやそれに類似する状況を指しています。

「期待しているような形にならない」ということは、当然ですがどこかに問題があるわけですよね。

  1. その問題がどこにあるのか(問題のあるところ、無いところの切り分け)
  2. その問題をどのように対処すればよいのか

この2点を明らかにしなければなりませんね。
VLOOKUPで困っているみなさんもこの2点について考えてみることが大事だと思います。

なお、当ページを確認しながらガチャガチャとファイルを修正することもあるかと思います。
「どこに何をしたのか分からないー」となっても大丈夫なように、念の為ファイルのバックアップを取っておくことをオススメします。

確認のポイントして「このエラーが出たから、この対処でよい」と言い切れないことがあることに気をつけてください。
というのも、状況が違うにも関わらず、同じエラーが表示されるケースもあるからです。
例えば次の内容です。

同じエラー表示でも違う解決方法になることも・・

こちらは後述しますが、例えば一般的にVLOOLUP関数で表示される「#N/A」のエラーがあります。このエラーは、値が見つからないときに表示されるエラーです。
しかし、この点だけに捉われると解決に向かわない場合もあります。

例えば、以下のVLOOKUP関数の使い方ですが、上記の内容とは明らかに違うにも関わらず、同じエラー「#N/A」が発生します。

関数の中の1つ目の引数が無い

これは「#N/A」のエラーに限った話ではなく他のエラー、そしてVLOOKUP以外の関数でも同様ですね。

エラーで表示される内容は、エラーを解決するために非常に役に立ちます。が、このような例外的にも見えるケースも起こり得るためご注意です。

「素のVLOOKUP」にして作業(確認)する

VLOOKUP関数は、IFERRORと一緒に使われることや、引数を別の関数で覆う(ネストする)使い方もあります。

ネストにすると原因がわかりづらくなるので切り分けると良い

うまくいかない問題を「より早く」見つける手段として、できるだけ簡素化した状態で確認します。
このための方法として「VLOOKUP単体」にしておくことをお勧めします。

状況に応じてネストを避け、関数や数式を単体にしておくこと。これは問題解決だけでなく、作業の可視化、可読性の向上にも貢献すると思います。

ネストをやめて外に出すと、普通は作業列が必要になります。そこが美しくないところ、あるいは気になるところとして出てきます。

作業列を作り、ネストしている関数を外に出す

ですが、こうすることで・・
「VLOOKUPが間違えているのか」、あるいは「VLOOKUP以外の部分(ネストしていた関数など)が間違えているのか」の判断を明確にできるために有効です。

言い換えると、思うようにいかない、思うようにならない犯人・・実は「VLOOKUPじゃなかった(汗)」というケースを見極めるためです。

VLOOKUPでのエラーの確認箇所は大きく2箇所

VLOOKUP関数でエラーになったとき、確認すべき箇所は大きく2つの箇所になります。

  1. VLOOKUP関数で使用している引数4個
  2. VLOOKUP関数で参照している表

1.VLOOKUP関数で使用している引数4個

VLOOKUPの引数は4個ある

全部大事ですが、1個目の引数は、通常は「セルを選択」するケースが多いですね。何か問題があるときは、ここを直接「文字として入力(手入力)」して試すのもありだと思います。

2.VLOOKUP関数で参照している表

他の表をVLOOKUP関数で使用する

上記例は、右の表(濃紺)にVLOOKUP関数を入れています。価格の値を左の表(黄色)から持ってきています(参照しています)。
VLOOKUP関数が思うようになってないとき、この左の表(黄色)も確認の対象としなければならないケースもあります。
※上記の画像は、説明の便宜上、1つのシートに書いていますが、実際はそれぞれ別のシート(上記の場合は2シート)に書くケースが多いと思います。

実際のところ、上記の確認内容は重なる部分もあり、「1」を見ることで、結果的に「2」も見ているという部分もあります。そのため、厳密ではないのですが目安として押さえておくと良いと思います。

VLOOKUP関数のエラーに限ったことではありませんが、エラーになったとき、色々な要素が絡み合ってるケースがあります。

最初(前半)はOK(問題なし)、後(後半)でNG(問題あり)のケースもあるんですね。クイズみたいな話ですが実際にあり得ます。
例えば、参照範囲でいえば、「相対参照」にしていると、先頭(行)部分は正しくても、オートフィルで数式をコピーした時に末尾(行)部で発生するエラーなどがありえます(詳細は後述)。

それでは以下にTIPS的に説明します。
以下の解決策にピンポイントで当てはまらない場合は、つまるところ全部ご確認くださいませ、です(笑)

VLOOKUPを入れたセルに「#N/A」と表示される場合

このエラーが表示される場合、VLOOKUPの式の構成は間違っていないことが考えられます。先ほどは、このエラーの例(相対参照がずれる)として挙げました件ですが、(私の感覚ですと、)このエラーの原因は、「値が見つからない」エラーの方が多いように思います。

入力したVLOOKUP関数は懸命に「値を探した。だけど値が見つからなかった」というエラー内容です。

つまり・・

「検索する値が見つからなかったから#N/Aを表示している」

です。

#N/Aは「値が見つからない」ときのエラー


これは言い換えれば、VLOOKUP関数は正常に動いているとも言えます。

もしも「値は必ずある(存在する)」ということであれば、このエラーは表示されません(表示されないはず)
※実際には「データはちゃんとある。だけど表示されない」ことも起こりえます。これは他の項目も参照ください。

この点での確認すべき箇所は、VLOOKUP関数の第1引数と第2引数です。
この2つの引数は連携しあっています。一方が正しくても、もう一方が不正確だと予想しない結果になります。

VLOOKUPを入れたセルの行が途中から「#N/A」と表示される場合

先ほどの確認でVLOOKUPの式の構成は間違っていないと言いました。しかし、これは半分正解で半分不正解になり得ます。
それは、

「式は間違っていない。が、そもそも引数に設定する値を間違えている

ケースです。
具体的には以下の内容です。
最初のセルのVLOOKUPは正しいです。しかし、問題は関数のコピーです。
下の行にコピーをしていくと、「参照」がずれるのです。

関数の参照が相対のときにオートフィルでコピーすると・・
相対参照で設定しているときは、コピーすると式がずれていくことに注意する

ポイント(問題)は参照範囲が「相対参照」になっていることです。

コピーするたびに参照範囲がずれていくケース

上記、1月3日のメロンの検索では、価格が見つからないことになっています。
参照範囲が1行ずつずれていることが原因です。

この対応方法は2つです。

  1. $(ドル)記号を入れて参照範囲がずれないようにする
  2. 列全体を選択して、参照範囲がずれないようにする

結果的にはどちらでも同じですが、「2」のほうは関係ないセルを含みます。

行の削除に影響を受けないのと、設定が楽なので私は「2」を好みます。

いずれにしても、参照範囲が固定されてないため、コピーを続けると、VLOOKUP関数の入っているセルが1行ずつずれるごとに参照範囲もずれていきます。このため結果的に本当は値があるにも関わらず、(気づかぬうちに)範囲から漏れているため、#N/Aと表示するという流れです。

VLOOKUPを入れたセルに「#VALUE」と表示される場合

次に「#VALUE」エラーと表示される場合です。
このケースは、引数によって引き起こされることが多いです(他にもありますが、それは「その他」に含めています)。

#VALUEのエラー

この場合は、引数を確認してみてください。
上記の画像の場合は、「0」と入れていることが原因です。ここの入力には「1」以上の値ですね。
なお、繰り返しますが、ここで提示している例は引数3ですが、他の引数でも設定した値によっては同じエラーが発生する可能性がありえます。念の為、他の引数も同様に確認してください。

VLOOKUPを入れたセルに「#NAME」と表示される場合

これは、名前に関するエラーです。

#nameエラー

こちらは、「VLOOKUP」の名前が間違えているケースです。


こちらは参照する表に名前をつけています。しかし表の名前が違うことによってエラーが表示されています。

「名前定義」を間違えているケース

このように名前を間違えるとエラーになってしまいます。
この対応方法は「正しい名前を設定する」です。そのままですね(笑)。

VLOOKUPを入れたセルに「#REF!」と表示される場合

このエラーは、「参照エラー」ですね。
これは「他のセルを参照している引数」が怪しいと考えられます。
すなわち、よくあるケースの「引数1と引数2」ですね(その他の引数もセル参照できます)。

これは、私の経験と想像ですが、もともとちゃんとできていたにも関わらず、このエラーは「気づいたら」、「いつの間にか」、「突然」発生したケースが多いように思います。
というのも、VLOOKUP関数を入れている段階で起こりにくいエラーと思うからですね。

もしかしたらこのエラーでも色々なケースで発生する可能性あるかもしれませんが、よくあるのは「削除」に絡んだ操作をしたときだと思います。

VLOOKUP作成時にはあった列を作成後に削除したケース

上記のように「もともと参照済みの列を削除」すると、このエラーが発生します。

VLOOKUPの文字そのものが表示される

これはVLOOKUP関数を入れている「セルの書式の設定」の問題ですね。

セルの書式設定が「文字列」になっていると関数が動かない
VLOOKUPの文字が表示されるときはセルの書式設定を確認する

この場合、セルの書式設定を「標準」などにすれば機能しますが、セルの書式設定を変えただけだと、まだ変わらない(文字のまま)のですよね。
変更したセルにカーソルなどをおくと関数として動き出します。

セルの書式設定を標準にした後、セルを更新させる
書式を変えただけでは反映されない

CSVファイルなどをExcelに文字列として読み込み、その状態で関数や数式を入れるとこの手のエラーが起こりやすいと思います。

0と表示される

これはVLOOKUP関数で検索した値が見つかったものの、それに対応する(セルの)値が空白のケースですね。

見つかった値が空白のときは0表示となる

これはVLOOKUP以外の関数でも同様に発生しますね。
次の形にすると0は消えます。

空の文字列と連結させると0が消える

式の最後に「&””」とダブルクォーテーションを2つ入れて、空白(0文字)の文字列を追加すると解決できます。

これはVLOOKUP関数の結果で値が正しく表示されるセルで行っても大丈夫です。

その他のエラー:引数などを変えても「エラー表示」がある

エラー表示のやっかいな部分ですが、上記で示したエラーとその解決方法は、わりと有効な方法だと思いますが、それでもうまくいかない場合もありますね。
そういうとき、参照している表自体がエラーの原因になる場合があります。

例えば、そもそも参照している表の中でも関数を使っているケースとして以下を見てみます。

参照している表そのものにエラーが発生しているケース

※上記左の表のエラーは便宜的にセルに手動で入力しています

かんたんに言うと、そもそも「参照している表にエラーが発生している」場合、この表をVLOOKUPで参照すると、やはりエラーが表示されます(これは他の関数でも同様ですね)。

対応としては、左の表の値を修正です。

その他のエラー:値はある・・でも値が見つからない(表示されない)

この場合は、色々なケースが考えられそうです。
例えば、以下のケースです。「すいか」がエラーになっています。

見ただけでは分かりにくいエラー

「値はある、なぜ見つからない?、なぜ100と表示されないの?」
この問題点は、左側の表にあります。

よく見ると末尾に空白が入っているため「4文字」扱いとなっている

このように空白文字が末尾にあるため、検索しても見つからなかったということになります。
問題発生事後にこれに気づくのは至難ですがVLOOKUPを使う前に確認するなどして対処しておく必要があります。

ここで注意する点としては、空白文字が原因という以外にもというか、「文字が見つからなかった理由は文字そのもの、文字の種類が違う」など広く見ておくことですね。
例えば、以下は空白文字のエラーと類似しているエラーです。「空白文字の存在」だけに注意を向けていると発見が困難になります。

検索している文字が異なるため別物扱いされている

上記はひらがなとカタカナの違いですね。これは例なのでわかりやすいかもしれませんが、専門用語や似た文言、文字(「〇△□株式会社」と「株式会社〇△□」)なども注意ですね。

全てのセルに統一したエラーであれば、置換やTRIM関数などを使うことで対応できます。しかし、そのような規則性がなければ、これは一つ一つ見て直すしかないように思います。
ちょっと重い気持ちになりますが。。

その他のエラー:値はある・・でも表示内容が違う

これは、考えられるのが書式の設定の問題です。
期待したい結果は、「左の表(黄色)の価格」と「VLOOKUPのある右側の表(濃紺)の価格」が同じであってほしい、だと思いますが実際にはセルの書式設定で「見た目上」表示を変えることができます。

書式設定を変えると正しい関数の結果でも誤っていると勘違いしやすい

上記の2つの画像の赤枠の部分をセルの書式設定を使って表示を変えたところです。
1つ目の画像では、VLOOKUPは正しく機能しています。しかし書式設定を変えていることで、期待した形になっていないだけです。
2つ目の画像は参照している表の書式を変えています。が、こちらもVLOOKUPは正しく機能し、書式が標準になっているので表示も期待どおりですね。

共に間違えているように見えますが、表示の仕方を変えただけで、値は正しく拾えている(取得できている)ということにご注意ください。

セルの書式設定はいろいろな値の「見た目」を変えることができる

左の表にも同じにように表示の仕方を変えています。
最後の「教えない」は嫌がらせに近いですね(笑)。実際ここまでは無いと思いますが、このように自由に変化させることができることがExcel機能のメリットでもある一方、こういうときには困るというジレンマがありますね。

なお、ずっと上の方で挙げていたエラーで「式そのものが表示される」も、このセルの書式設定が原因ですが、セルの書式の設定は、Excelの初期設定(自動変換)だけでなく、利用者側でもかなりいじれます。さらにセルの色やフォント変更で飾り付けまでできます。
何らかの設定されているとなかなか気づきにくいですね。

この書式の問題は、私の個人的な問題ですが、苦手ですし、嫌いです(笑)

というのも、書式を変えるセルを「狙い撃ち」できるため規則性が見出しにくいことが原因です(私のスキルの問題かもしれませんが・・)。

おまけに、書式により「VLOOKUPではできない、ただしCOUNTIFではできる」など読めない、読みにくい傾向もあるですね。

この点については思ったより複雑な日付を整理してみる(関数編)よりどうぞです。

もし、これらの確認事項を見てもうまくいかない場合、新しいシートにコピー(値貼り付けして既存の関数・数式を消す)してそれで試すや、VLOOKUPを作り直すなどしてお試しください。

業務で使っているなら、「できないから無理!」とさじを投げるわけにはいかないと思います。
どうぞ諦めることなく・・うまくいきますように!

タイトルとURLをコピーしました