VLOOKUPで商品名を引いたら、#N/Aが並ぶ。探しに行く表に同じコードがあるのに、なぜか見つからない。下の行にコピーしたら、途中から#N/Aが増えた。
最初に確認したいのは、#N/Aがすべての行に出ているのか、一部の行だけかです。
なお私もVLOOKUPは作りながら調べてしまうので、ぜひ以下を参考にしてみてください。
この記事でわかること
- VLOOKUPで#N/Aになる6つの原因と直し方
- 突き合わせの漏れを、報告の前に見つける方法
結論：「数式」と「データ」に分けて探す
VLOOKUPの#N/Aは、数式の3つとデータの3つに分けて探すと、原因をしぼれます。
VLOOKUPの#N/Aは、探す値が見つからなかったという意味です。探しに行く表(商品マスタなど)を、この記事では「相手の表」と呼びます。
見る場所ごとの原因は次のとおりです。
- 数式：検索の型・探す列の位置・範囲のずれ
- データ：数値と文字列・空白と表記の違い・相手の表にない
まずここから確認：#N/Aの出方から、6つの原因をしぼる
#N/Aがどの行に出ているかで、最初に疑う原因が決まります。
この記事では、1つのExcelファイルにある架空の2つのシートを例にします。
1つ目は「売上表」です。売上を1行ずつ記録したシートで、C列の商品名をVLOOKUPで引きます。
| A列:商品コード | B列:数量 | C列:商品名 |
|---|---|---|
| 1001 | 3 | (VLOOKUPで引く) |
| 1003 | 1 | (VLOOKUPで引く) |
| 1002 | 2 | (VLOOKUPで引く) |
2つ目は「商品マスタ」です。商品コードごとに、商品名と単価をまとめたシートです。
| A列:商品コード | B列:商品名 | C列:単価 |
|---|---|---|
| 1001 | ボールペン | 120 |
| 1002 | ノート | 200 |
| 1003 | ファイル | 150 |
売上表のA列の商品コードで、商品マスタから商品名を引きます。数式は売上表のC2に入れ、下の行へコピーします。どちらの表も、1行目は見出しで、データは2行目からです。
=VLOOKUP(A2,商品マスタ!$A$2:$C$4,2,FALSE)
4つの引数(関数の( )に入れる値)は、次のとおりです。
- 検索値：探す値。ここでは売上表のA2
- 範囲：探しに行く表。商品マスタの2〜4行目
- 列番号：範囲の左から何列目を返すか
- 検索の型：FALSEで完全一致、TRUEで近似一致
疑う原因と最初に見る場所を、#N/Aの出方ごとにまとめると次のとおりです。画面の名前は、Windows版のMicrosoft 365を前提にしています。
| #N/Aの出方 | 疑う原因 | 最初に見る場所 |
|---|---|---|
| #N/Aのほか、違う値が返る行もある | 1. 検索の型 | 数式の4番目の引数 |
| すべての行が#N/A | 2. 探す列の位置 | 範囲の1列目 |
| 下の行ほど#N/Aが増える | 3. 範囲のずれ | 下の行の数式の範囲 |
| 新しく足したコードだけ#N/A | 3. 範囲のずれ | 範囲の最後の行番号 |
| 数字だけのコード(1001など)が、あるのに#N/A | 4. 数値と文字列 | セルの揃え・緑の三角 |
| 英字入りのコード(A-1001など)が、あるのに#N/A | 5. 空白と表記の違い | 検索値の文字数 |
| 上のどれでもない一部の行だけ#N/A | 6. 相手の表にない | 相手の表を検索 |
この表の1〜3は数式、4〜6はデータの原因です。
どれに当たるか分からないときは、表の上から順に見ます。1〜3は、数式バー(シートの上にある、セルの中身を表示する欄)の数式と相手の表を見るだけで確かめられます。
原因と直し方：数式の3つ、データの3つ
6つの原因は、数式とデータのどちらを直すかが決まっています。
原因1：検索の型を省略している・TRUEにしている
問題と原因：検索の型を省略すると、TRUE(近似一致)として扱われます。近似一致は、範囲の1列目が昇順(小さい順)に並んでいる前提で、検索値以下で一番大きい値を返す探し方です。
並んでいない表では、#N/Aになったり、違う商品名が返ったりします。違う値が返る行はエラーにならないため、気づきにくい問題です。
確認方法：数式の4番目の引数を見ます。何も書いていないか、TRUEか1なら当てはまります。
どう直すか：4番目の引数にFALSEを入れ、完全一致にします。=VLOOKUP(A2,商品マスタ!$A$2:$C$4,2) なら、2 の後ろに ,FALSE を足します。
原因2：探したい列が、範囲の左端にない
問題と原因：VLOOKUPは、範囲の1列目だけを探します。たとえば相手の表のA列が登録日、B列が商品コードだと、A列から始まる範囲ではコードが見つかりません。
この場合、すべての行が#N/Aになります。
確認方法：範囲の1列目に、検索値と同じ種類の値(ここでは商品コード)が入っているかを見ます。
どう直すか：範囲を、商品コードの列から始めます。B列にコードがあるなら、$B$2:$D$4 のようにB列から始まる範囲にします。列番号も、新しい範囲の左から数え直します。
返したい列が商品コードより左にあるときは、相手の表で商品コードの列を左へ移します。列を切り取り、移したい場所の列を右クリックして「切り取ったセルの挿入」を選びます。
原因3：範囲がずれている・最後の行まで届いていない
問題と原因：範囲に $ を付けずに数式を下へコピーすると、範囲も一緒に下へずれます。売上表の3行目の数式では、範囲が 商品マスタ!A3:C5 になり、商品マスタの2行目が外れます。
4行目の数式では 商品マスタ!A4:C6 になり、1002がある3行目も外れるため、#N/Aになります。
外れた行のコードを探す行だけが#N/Aになるため、下の行ほど#N/Aが増えます。
商品マスタの5行目以降に足したコードも、範囲の外なので見つかりません。
確認方法：#N/Aが出ている行の数式を選び、数式バーで範囲の行番号を見ます。範囲の最後の行が、商品マスタの最後の行まで届いているかも見ます。
どう直すか：数式バーで範囲を選んで、F4キーを押します。$A$2:$C$4 のような絶対参照(コピーしても動かない参照)になります。直したら、もう一度下の行へコピーします。
行が増えていく表なら、範囲を列全体の 商品マスタ!$A:$C にする方法もあります。
原因4：数値と文字列が混ざっている
問題と原因：同じ「1001」でも、数値の1001と文字列の「1001」は別の値として扱われます。別のシステムから取り込んだ表では、コードが文字列で入っていることがあります。
確認方法：文字列の数値は、セルの左上に緑の三角(エラー インジケーター)が出ることがあります。揃えを変えていなければ、文字列は左揃え、数値は右揃えです。空いているセルに =ISTEXT(A2) と入れ、TRUEなら文字列です。
どう直すか：数値にそろえるなら、セルを選ぶと横に出る「!」のボタンから「数値に変換」を選びます。表示形式を変えるだけでは、入っている値が変わらないことがあります。
「0012」のように先頭に0がつくコードは、数値にすると0が消えます。この場合は、両方の表を文字列にそろえます。
数式の中でそろえる方法もあります。検索値が数値で相手の表が文字列なら、VLOOKUPの1つ目の引数 A2 を A2&"" に書き換えます。逆なら VALUE(A2) にします。
文字列の数値を列ごとに見つける方法(COUNT関数とCOUNTA関数の比べ方)は、次の記事にまとめています。
原因5：余分な空白や、表記の違いがある
問題と原因：完全一致では、文字がすべてそろっているときだけ見つかります。「A-1001 」のように末尾に空白があると、「A-1001」とは別の値です。
ハイフンの種類や、全角と半角の違いも、別の値として扱われることがあります。
確認方法：空いているセルに =LEN(A2) と入れ、文字数を数えます。「A-1001」なら6のはずです。相手の表のセルも同じように数えます。
どう直すか：TRIM関数で余分な空白を取り除きます。空いている列に =TRIM(A2) と入れ、下の行へコピーします。
その列をコピーし、A列に値として貼り付けます(右クリックの「貼り付けのオプション」で「値」)。相手の表に空白があるときは、相手の表で同じようにします。
表記の違いは、置換(Ctrl+H)などで1つの書き方にそろえます。
原因6：探す値が、相手の表に本当にない
問題と原因：相手の表に登録していない新しいコードは、どう書いても見つかりません。
検索値のセルが空の行も、#N/Aになることがあります。入力前の行まで数式をコピーしてあると起きます。
確認方法：#N/Aの行のコードを、相手の表のシートでCtrl+F(検索)で探します。「オプション」で「セル内容が完全に同一であるものを検索する」に印を付けると、一部だけ同じコードを除けます。
どう直すか：登録漏れなら、相手の表に足します。相手の表にないのが正しい行(取り扱いをやめた商品など)は、その行を集計から外すか、理由を書き残しておきます。
それでも#N/Aが消えないとき：2つのセルを「=」で直接比べる
6つを確かめても#N/Aが消えないなら、検索値と相手の表のセルを「=」で直接比べます。
空いているセルに =A3=商品マスタ!A4 のように入れます。売上表のA3(1003)と、同じ1003に見える商品マスタのA4を比べる例です。
- FALSEなら：データが違う(原因4・5)
- TRUEなら：数式が違う(原因1〜3)
FALSEで、空白も表記も同じに見えるときは、TRIM関数で取れない文字が入っていることがあります。=LEN() で両方の文字数を比べ、多い方をCLEAN関数(印刷されない文字を取り除く関数)や置換で直します。
数式の途中の計算は、数式のセルを選び、「数式」タブの「数式の検証」で1段ずつ見られます。
数式を直しても結果が変わらないときは、計算方法が手動になっていることがあります。数式が計算されない原因は、次の記事で確かめられます。
業務への着地：突き合わせの漏れを、報告の前に見つける
VLOOKUPの#N/Aは突き合わせで漏れた行の印なので、報告の前に確かめると、集計漏れを防げます。
VLOOKUPは、売上表に商品名や単価を付けるときや、名簿どうしを照らし合わせるときに使われます。どちらも2つの表の突き合わせで、#N/Aの行は相手の表に同じ値が見つからなかった行です。
#N/Aの行を見落としたまま集計すると、漏れた行が商品ごとの合計に入りません。その数字が報告書(会議や上司に出す集計表)に載ると、一部の商品だけ少なく見えたまま話が進みます。
#N/Aの行は、「データ」タブの「フィルター」で#N/Aだけを表示すると、まとめて確かめられます。単価などもVLOOKUPで引いているなら、その列をSUMで合計すると結果も#N/Aになります。漏れに気づくきっかけにもなります。
比べると次のとおりです。
| 場面 | Before | After |
|---|---|---|
| #N/Aが出たとき | 見落として集計する | 出方から原因をしぼる |
| 報告書に写す前 | そのまま写す | #N/Aの行が残っていないか確かめる |
この表では、漏れに気づくのが報告書を出す前になっています。
#N/Aの行を確かめてから写すと、出した後に数字を直す手間が減ります。見つからなかったコードが分かれば、相手の表の登録漏れも直せます。翌月の突き合わせで、同じ#N/Aを探さずに済みます。
商品ごとの合計を足して全体の合計と比べる検算は、次の記事で紹介しています。
まとめ
VLOOKUPで#N/Aになるときは、数式(1〜3)→データ(4〜6)の順に確認します。
- 検索の型がFALSEになっているか
- 探す列が範囲の左端にあるか
- 範囲に
$が付き、最後の行まで届いているか - 数値と文字列がそろっているか
- 余分な空白や表記の違いがないか
- 相手の表に本当にあるか
それでも消えないときは、2つのセルを「=」で直接比べます。報告の前に、#N/Aの行が残っていないかを確かめます。
次に読む記事
VLOOKUPは、「Excelできます」の中級の目安によく挙がる関数です。関数を覚えた先で業務に求められることは、こちら。
関数の基本や表の整え方から確かめたいときは、こちらから。
