VLOOKUPが#N/Aになる5つの原因と直し方【計算して確認済み】
VLOOKUPを使っていて、見た目は合っているのに#N/Aが出る場面は多いものです。原因はほとんどが、次の5つのどれかです。それぞれ、実際に小さな表で計算して、症状と直し方を確かめました。
先に結論:確認する順番
- 検索値や表に、目に見えないスペースが入っていないか
- 検索値と表の側で、数値と文字列が食い違っていないか
- 第4引数を
FALSEにしているか(完全一致) - 検索値が、範囲の「一番左の列」にあるか
IFERRORで、原因を隠してしまっていないか
原因1:末尾や先頭のスペース
商品コードA002を探したいのに、検索値がA002(末尾に半角スペース)になっていると、表のA002とは別の文字列として扱われます。
=VLOOKUP(G1, A1:B3, 2, FALSE)
この式は、G1がA002だと#N/Aになりました。次のようにTRIMで余分なスペースを取ると、正しくノートが返りました。
=VLOOKUP(TRIM(G1), A1:B3, 2, FALSE)
表の側にスペースが混ざっている場合は、表のデータをTRIMで整えた列を別に作るのが確実です。コピーしたデータや、システムから出力したデータで特によく起こります。
原因2:数値と文字列の食い違い
見た目は同じ1002でも、片方が数値、もう片方が文字列だと一致しません。検索値が数値の1002で、表の側が文字列の1002だと、#N/Aになりました。
=VLOOKUP(G2, D1:E2, 2, FALSE)
検索値の後ろに空文字を連結して文字列に揃えると、みかんが返りました。
=VLOOKUP(G2&"", D1:E2, 2, FALSE)
逆に、表の側が数値で検索値が文字列のときは、VALUEで数値に変換します。根本的には、表の列の形式をそろえるのが一番です。Microsoftの公式ページも、データ型の不一致を原因として挙げています。
原因3:第4引数を省略している(近似一致)
第4引数を省略すると、TRUE(近似一致)として動きます。近似一致は、検索列が昇順に並んでいる前提の探し方です。並んでいない表で使うと、#N/Aや、間違った値が返る原因になります。
=VLOOKUP(G3, K1:L3, 2)
昇順に並べていない表で試すと、LibreOffice Calcでは#N/Aになりました。Excelでも、並び順によっては#N/Aか誤った値になります(この点はExcelでの結果を私は確認していません)。コードや名前を探す通常の用途では、必ずFALSEを指定します。
=VLOOKUP(G3, K1:L3, 2, FALSE)
この式では、正しくBが返りました。
原因4:検索値が範囲の一番左の列にない
VLOOKUPは、指定した範囲の「一番左の列」から検索値を探します。商品名が左、コードが右にある表で、コードから商品名を引こうとすると失敗します。
=VLOOKUP(G4, N1:O2, 1, FALSE)
この式は、#N/Aになりました。左側の列を取り出したいときは、INDEXとMATCHの組み合わせにします。
=INDEX(N1:N2, MATCH(G4, O1:O2, 0))
これでノートが返りました。この方法は、列の並びを変えても壊れにくいという利点もあります。
原因5(落とし穴):IFERRORでエラーを隠す
エラー表示を消すために、IFERRORを使うことがあります。
=IFERROR(VLOOKUP(G1, A1:B3, 2, FALSE), "該当なし")
実際に、原因1のスペース混入のケースで該当なしと表示されました。見た目はきれいですが、本当は「スペースのせいで見つからなかった」だけで、データの問題は直っていません。Microsoftの公式ページも、IFERRORはエラーを隠すだけで、不足しているデータを補うものではないと説明しています。
エラーを隠す前に、まず原因1〜4を確認するのがおすすめです。本当に「該当なし」のときだけ表示したいなら、上のように原因を直したうえで、IFERRORを最後に使います。
原因を見つける診断のしかた
直し方を試す前に、どの原因なのかを数式で確かめると、遠回りしません。次の確認用の数式を、空いているセルに入れてみてください。検索値がA002(末尾にスペース)、表の側がA002の場合の結果です。
| 確かめたいこと | 数式 | 結果 |
|---|---|---|
| 検索値の文字数 | =LEN(A1) |
5 |
| 表側の文字数 | =LEN(A2) |
4 |
| 2つのセルは同じか | =A1=A2 |
FALSE |
| 大文字小文字も含め同じか | =EXACT(A1,A2) |
FALSE |
| スペースを除けば同じか | =EXACT(TRIM(A1),A2) |
TRUE |
| 検索値は数値か | =ISNUMBER(B1)(B1が数値の1002) |
TRUE |
| 表側は数値か | =ISNUMBER(B2)(B2が文字列の1002) |
FALSE |
| 表側は文字列か | =ISTEXT(B2) |
TRUE |
文字数が見た目と合わなければ、スペースの混入です。ISNUMBERが食い違っていれば、数値と文字列の違いです。このように、「見た目は同じなのに一致しない」ときは、文字数と型を確認するのが近道です。
予防しておくとよいこと
- 検索に使う列は、最初から文字列か数値かを決めて、形式をそろえておきます。
- 他のシステムから貼り付けたデータは、検索の前に
TRIMをかけた列を作ります。 - 検索の式は、第4引数を必ず書きます。「省略しても動く」状態を作らないことが、後から原因を探す手間を減らします。
- 表が大きいときは、
COUNTIFで「そのコードが表に何件あるか」を数えておくと、そもそも存在しないのか、形式の問題なのかを切り分けられます。
まとめ
#N/Aの主な原因は、スペース、型の違い、近似一致、検索列の位置の4つです。- 検索の基本形は
=VLOOKUP(検索値, 範囲, 列番号, FALSE)です。 - 左側の列を取り出すときは、
INDEXとMATCHを使います。 IFERRORは、原因を直してから、最後に使います。
なお、この記事の数式は、LibreOffice Calcで実際に再計算して結果を確認しています。お使いの環境(Excelのバージョンなど)によって、結果が異なる場合があります。