VLOOKUPやXLOOKUPで「#N/A」が出るときに確かめる順番

VLOOKUP や XLOOKUP で、あるはずの値なのに「#N/A」が出る。この表示は「探した値が見つからなかった」という意味です。原因はいくつかありますが、次の順番で確かめると、途中で見つかることが多いです。

■ 1. その値は、本当に元の表にあるか

まず、探している値を元の表で検索してみてください。元の表にまだ登録されていないコードや、入力ミスであれば、直すのは数式ではなくデータの側です。

■ 2. 前後に空白が入っていないか

たとえば（架空の例です）「A001」と「A001 」は、見た目は同じでも別の値として扱われます。ほかのシステムから書き出したデータや、貼り付けたデータに入りやすい違いです。TRIM 関数で前後の空白を取り除いた列を作り、そちらで探すと確かめやすくなります。

■ 3. 数値と文字列が混ざっていないか

片方では数値の「1001」、もう片方では文字として入った「1001」になっていると、一致しません。Excel なら、セルの書式設定（Ctrl+1）で表示形式を確かめられます。どちらかにそろえてから、もう一度試してください。

■ 4. 「完全一致」で探しているか

VLOOKUP は、4つ目の引数を省くと「近似一致」になります。Googleスプレッドシートの VLOOKUP も同じです。コードや番号を探すときは、4つ目に FALSE を入れて完全一致にしてください。近似一致のままだと、#N/A のほか、違う行の値が返ってくることもあります。XLOOKUP は、何も指定しなければ完全一致で探します。

■ 5. 探す範囲がずれていないか

VLOOKUP は、指定した範囲のいちばん左の列で値を探します。探したい列が左端に来ているかを見てください。また、数式を下にコピーしたとき、範囲も一緒にずれていないか確かめます。「$A$2:$B$100」のように $ をつけて固定すると、コピーしても同じ範囲を見続けます。

■ エラーを隠すのは、最後に

IFNA や IFERROR を使うと、#N/A の代わりに空白や「該当なし」と表示できます。XLOOKUP なら、4つ目の引数に表示したい文字を入れられます。ただ、1〜5を確かめる前に隠してしまうと、本当は見つかるはずの行まで「該当なし」になり、間違いに気づきにくくなります。

確かめても原因が分からないときや、表の形に合わせた数式を作ってほしいときは、下のサービスをご覧ください。数式を作って再計算で結果を確かめ、どのセルに何を入れたかの説明をつけてお返しします。名前や番号はダミーに置き換えてお送りください。