おはよう、後輩A君。
前に別のシートから数字を持ってくる話を書いたよね。あれは「同じ場所にある数字を、別のシートから引っぱる」話だったんだ。
今日はその続きだよ。「このコードの、商品名を持ってきて」――場所じゃなくて、中身で探して持ってくるやり方なんだ。VLOOKUPというんだよ。
⚠️ ただね。これがエクセルでいちばん「#N/A」を見る関数なんだ。しかも困るのは、ちゃんとデータが入っているのに「無い」と言われることなんだよね。
私も昔、目で見て確かに入っているコードを、疑って何度も打ち直したことがあるんだ。悪いのは自分の打ち方じゃなかったんだけどね。
だから今日は、実際にエクセルで9パターン作って、1つずつ答えを測ったよ。#N/Aが出た原因は6つだったんだ。全部出すね。
1. まず、動く形を1つ覚えようか
こんな表で説明するね。
商品コード 商品名 単価
2行目 103 マウス 1200
3行目 101 キーボード 3500
4行目 105 モニター 18000
5行目 102 USBメモリ 980
6行目 104 ケーブル 450
⚠️ この表、コードが順番に並んでいないよね。現場の表はたいていこうなんだ。追加した順に増えていくからね。これがあとで大事になるよ。
「102の商品名を持ってきて」と書くと、こうなるんだ。
=VLOOKUP(102,A2:C6,2,FALSE)
測った答えは USBメモリ だったよ。単価がほしいなら3列目だから、こうだね。
=VLOOKUP(102,A2:C6,3,FALSE) → 980
中に入っている4つの部品は、こういう意味なんだ。
① 102 探す値。「これを見つけて」
② A2:C6 探す場所。表のどこからどこまでか
③ 2 答えを返す列。範囲の中で何列目か
④ FALSE ぴったり一致したものだけ返して、という指定
⚠️ ④の FALSE は、面倒でも毎回書いてほしいんだ。省略すると、エラーも出さずに別の商品名が返ってくることがあるんだよ。これは今日の#N/Aとは別の怖さだから、次の記事で測った結果ごと書くね。
今日はここから、#N/Aが出たときに見る6か所の話だよ。
2. ⚠️ 探す列が範囲の左端にないと、見つからないんだよ
いちばん多いのがこれだと思うんだ。
さっきと同じデータで、列の順番だけ入れ替えた表を作ったよ。A列が商品名、B列がコードだね。
商品名 商品コード 単価
2行目 マウス 103 1200
3行目 キーボード 101 3500
この表に、さっきと同じ気持ちで書いたんだ。
=VLOOKUP(102,A2:C6,1,FALSE) → ⚠️ #N/A
102はB列にちゃんと入っているのに、無いと言われるんだよ。
理由はこれなんだ。
VLOOKUPは、範囲のいちばん左の列しか探さない
範囲を A2:C6 と書いたから、探しにいったのはA列（商品名）なんだよ。商品名の中に102は無いよね。だから「無い」で正しいんだ。
直し方は、探す列から範囲を始めることだよ。
=VLOOKUP(102,B2:C6,2,FALSE) → 980
B列から始めたから、B列を探してくれるんだ。単価はB列から数えて2列目だね。
⚠️ でもここで気づくことがあるんだ。この形だと、商品名（コードより左にある）は返せないんだよ。VLOOKUPは左から右にしか持ってこられないんだ。
これを解決する道は2つあるよ。
① 表の列の順番を変える → 探すものを左端に置く
② 別の関数を使う → XLOOKUP、またはINDEXとMATCHの組み合わせ
⚠️ 私は①をすすめるんだ。表の形を「探すものが左端」にしておくと、この先ずっと迷わないからね。関数を増やして解決するのは、表を直せないときだけでいいんだよ。
3. ⚠️ 範囲に「$」を付けないと、下にコピーしたとき崩れるんだよ
これは数式をコピーしたらズレる話と同じ根っこだよ。でもVLOOKUPだと崩れ方が分かりにくいんだ。
範囲に$を付けないまま、式を下に2つコピーして測ったよ。
1行目（102を探す） → USBメモリ 正しい
2行目（104を探す） → ケーブル 正しい
3行目（101を探す） → ⚠️ #N/A
⚠️ 上の2行は合っているんだよ。ここが怖いところだね。
式が下にコピーされるたびに、範囲も一緒に下がっていくんだ。
1行目の範囲 A2:C6 ← 表ぜんぶ入っている
2行目の範囲 A3:C7 ← 1行目（103マウス）がこぼれた
3行目の範囲 A4:C8 ← 101キーボードもこぼれた
探していた101は、もう範囲の外に出てしまったんだよ。だから#N/Aなんだ。
⚠️ この壊れ方は、上から見ていくと気づけないんだよね。2行目まで合っているから「式は合っている」と思うんだ。そして下のほうだけ#N/Aが並ぶんだよ。
直し方は、範囲に画鋲を刺すことだよ。
=VLOOKUP(E2,$A$2:$C$6,2,FALSE)
$は手で打たなくていいよ。式の中で範囲を選んでから F4キー（Macは command + T）を押すと付くんだ。⚠️ VLOOKUPの範囲は行も列も両方止める（$A$2:$C$6）のが基本だよ。条件付き書式の$とは付け方が違うから、そこは混ぜないでね。
4. ⚠️ 範囲が表より短いと、あるのに「無い」と言われるんだよ
3章と似ているけれど、こっちは最初から範囲が足りていない場合だよ。
表は6行目まであるのに、範囲をA2:C5と書いてしまった状態で測ったんだ。
=VLOOKUP(104,A2:C5,2,FALSE) → ⚠️ #N/A
=VLOOKUP(104,A2:C6,2,FALSE) → ケーブル
104は6行目にいるんだ。範囲が5行目で終わっていたから、届かなかったんだよ。
⚠️ これが起きるのは、たいていあとで表に行を足したときなんだ。式は自分で伸びてくれないからね。データを1行足しても、範囲は元のままなんだよ。
いちばん楽な直し方はこれだよ。
=VLOOKUP(104,A:C,2,FALSE) → ケーブル
列まるごと指定するんだ。これなら行が増えても届くよ。測っても、ちゃんと同じ答えが返ってきたよ。
⚠️ もう一つの手は、表をテーブルにしておくことだね（範囲を選んで command + T、Windowsは Ctrl + T）。テーブルは行を足すと範囲が自動で伸びるんだ。条件付き書式のときにも同じ理由でテーブルをすすめたよ。範囲が伸びない問題は、エクセルのあちこちで顔を出すんだよね。
5. ⚠️ 数字と文字列は、見た目が同じでも別物なんだよ
ここからは目で見ても違いが分からない原因だよ。
コードが文字列として入っている表を作って、数字の102で探してみたんだ。
数字の 102 で探す → ⚠️ #N/A
文字列の “102” で探す → USBメモリ
画面にはどちらも「102」と出ているんだよ。それでも、エクセルの中では別のものなんだ。
見分け方は簡単だよ。
セルの中で右に寄っている → 数字
セルの中で左に寄っている → 文字列
⚠️ 商品コードや社員番号は、文字列で入っていることが多いんだ。先頭の0を守るために、わざと文字列にしていることもあるからね（先頭の0が消える話に書いたやり方だよ）。
そろえ方は2つあるよ。
① 表と探す値のどちらかを直して、種類をそろえる（こっちが本筋）
② 式の中で変換する =VLOOKUP(TEXT(F1,”0″),$A$2:$C$6,2,FALSE)
②で測ったら、ちゃんと USBメモリ が返ってきたよ。⚠️ ただしこれは応急処置だね。表の中身が文字列なのは変わっていないから、次に誰かが同じ表を使うとまた引っかかるんだよ。直せるなら①にしようか。
6. ⚠️ 末尾の空白ひとつで、見つからなくなるんだよ
これがいちばん見つけにくい原因だよ。
表の側のコードに、うしろにスペース1つだけ足した状態を作ったんだ。画面上は「105」としか見えないよ。
=VLOOKUP(“105”,$A$2:$C$6,2,FALSE) → ⚠️ #N/A
中身を測ったらこうだったよ。
セルの中身 → 105␣（うしろに空白）
LEN で数えた長さ → ⚠️ 4
3文字のはずが4なんだ。これで見つからなくなるんだよ。
⚠️ 空白は選んでも見えないんだ。だから「入っているのに無いと言われる」の正体は、たいていこれなんだよね。
疑ったときは、まず数えようか。
=LEN(A4) → 思っている文字数と合っているか
合っていなかったら、TRIMで落とせるよ。
=VLOOKUP(TRIM(F1),$A$2:$C$6,2,FALSE)
⚠️ ただし表の側に空白がある場合は、表を直さないと解決しないんだ。探す値だけTRIMしても意味がないんだよね。空白の見つけ方と消し方は見えない空白の記事にまとめてあるよ。CSVから取り込んだ表では、ほぼ必ず出てくるんだ。
7. #REF! と #VALUE! は、#N/A とは違う話だよ
VLOOKUPで出るエラーは#N/Aだけじゃないんだ。2つ測ってきたよ。
列番号を 4 にした（範囲は3列） → ⚠️ #REF!
列番号を 0 にした → ⚠️ #VALUE!
この3つは、言っていることが違うんだよ。
#N/A 探したけれど、無かった
#REF! そこには返せる列が無い（範囲の外を指した）
#VALUE! 部品の形がおかしい（0列目なんて無い）
⚠️ だから#REF!が出たときに、データを疑っても意味がないんだ。数え間違いを疑うほうが早いんだよ。
列番号は「シートの何列目か」じゃなくて「範囲の中で何列目か」なんだ。範囲を B列から始めたら、B列が1列目になるんだよ。ここは2章とつながっているね。
エラー記号の読み方は#REF!・#VALUE!の記事にまとめてあるよ。
8. エクセルの記事、これで15本目だよ
① 指数表示を解除する
② 先頭の0が消える
③ 「1-2」が日付になる
④ 並べ替えで行がバラバラ
⑤ 数字なのに計算されない
⑥ エラー表示の意味
⑦ コピーしたらズレる
⑧ 見えない空白
⑨ 日付を引いたら1900年
⑩ 重複を消す前に
⑪ フィルタが途中で切れる
⑫ 別のシートから持ってくる
⑬ セルの結合でデータが消える
⑭ 条件付き書式
⑮ VLOOKUPで#N/A ← 今日
書いてきて気づいたことがあるんだ。今日の6つの原因のうち、3つは前に単独で記事にしたものだったんだよ。
$を忘れる → ⑦で書いた
見えない空白 → ⑧で書いた
数字と文字列 → ②と⑤で書いた
つまりVLOOKUPは、それまでの落とし穴が全部集まってくる場所なんだよね。だから難しく感じるんだ。関数が難しいんじゃなくて、関係する落とし穴の数が多いんだよ。
⚠️ 逆に言うと、ここを越えると前の困りごとにも強くなっているということなんだ。
結論：#N/Aは、6か所を順番に見ればいいんだよ
今日測った結果をまとめるとこうだよ。上から順番に見ていくと早いんだ。
① 探す列は、範囲のいちばん左にあるか
② 範囲に $ を付けたか（下にコピーすると崩れる）
③ 範囲は表の最後の行まで届いているか
④ 探す値と表の中身で、数字と文字列がそろっているか
⑤ うしろに空白が付いていないか（LENで数える）
⑥ #REF! と #VALUE! は別の話。列番号の数え間違いを疑う
私は昔、この順番を知らなかったからいつも④と⑤から疑っていたんだ。いちばん見つけにくいところから始めていたんだよね。
⚠️ ①から順に見ると、目で確かめられるものから片づくんだ。範囲の左端も、$も、範囲の終わりも、式を見れば分かるからね。見えないものを疑うのは最後でいいんだよ。
後輩A君も、#N/Aが出たらデータを疑う前に式を読んでみようか。打ち直す前に、範囲の左端を見るんだ。
⚠️ そしてもう一つだけ。#N/Aが出ていないからといって、合っているとは限らないんだよ。1章で「FALSEは毎回書いて」と言ったのはそのためなんだ。エラーを出さずに別の商品名を返すパターンを測ってきたから、それは次の記事で書くね。
よくある質問
Q1. XLOOKUPを使えばいいと聞きました
使えるならそのほうが楽だよ。左側も返せるし、範囲の左端という決まりもないんだ。⚠️ ただし古いエクセルでは開けないんだよね。XLOOKUPで作ったファイルを、まだ入っていない人に渡すと壊れて見えるんだ。社内で回すファイルなら、相手の環境を確かめてから決めようか。私は渡すファイルはVLOOKUP、自分用はXLOOKUPで分けているよ。
Q2. #N/Aを空欄にしたいです
IFERRORで包むとできるよ。
=IFERROR(VLOOKUP(F1,$A$2:$C$6,2,FALSE),””)
⚠️ でも原因を片づける前には使わないでほしいんだ。見えなくなるだけで、値は入っていないからね。空欄のまま集計すると、合計が静かに小さくなるんだよ。まず6か所を見て、それでも残る「本当に無いデータ」にだけ被せるのが順番だよ。
Q3. 式をAIに作ってもらってもいいですか
いいと思うよ。私も関数はAIに作ってもらう話を書いたよ。⚠️ ただ今日の6つは、AIに頼んでも起きるんだ。式の書き方の問題じゃなくて、表の中身の問題だからね。AIは「あなたの表に空白が入っている」とは知らないんだよ。だから式はAIに、原因さがしは自分でがちょうどいい分け方だと思うんだ。
Q4. 範囲を A:C と列まるごとにすると重くなりませんか
数百行から数千行なら気にしなくていいと思うよ。⚠️ 気になるのは、VLOOKUPの式が何千個も並んでいるときだね。そういうときは範囲をテーブルにするか、式の数を減らすほうが効くんだ。⚠️ 重さを疑う前に、まず動く形にすることを先にしようか。#N/Aが並んでいるファイルより、少し重くても正しいファイルのほうがいいからね。
Q5. 表の並び順は、直したほうがいいですか
今日の#N/Aに関しては並び順は関係ないよ。FALSEを書いていれば、バラバラの表でもちゃんと見つかるんだ（1章で測ったとおりだね）。⚠️ でもFALSEを省略した場合は、並び順で答えが変わるんだよ。ここが次の記事の話なんだ。
【本日のミッション：#N/Aを、上から順に疑ってみようか】
- ☐ 手元のVLOOKUPの式を開いて、探す列が範囲の左端にあるか見てみようか
- ☐ 範囲に $ が付いているか、そして表の最後の行まで届いているか確かめてみようか
- ☐ #N/Aが残ったら =LEN(セル) で文字数を数えて、空白がまぎれていないか見てみようか
- ☐ （後輩A君へ）#N/Aが出たとき、打ち直す前に式を読んでみようか。私は昔、入っているデータを何度も疑って、そのたびに自分の目を疑っていたんだ。⚠️ 悪いのは自分の打ち方じゃないことが、けっこうあるんだよ
📚 AI活用シリーズ（おすすめ）
コメント