もっと詳しく

値は存在しているのに#N/Aエラーになる

いつも使っているVLOOKUPが#N/Aエラーになり、困り果てました。エラーの原因は、

  • 検索値が検索範囲の左端の列にない。
  • 完全一致と部分一致の指定が誤っている。
  • 検索値を参照する列が昇順で並び替えられていない。
  • 小数で計算した値を検索値に使用している。
  • 検索範囲を「絶対参照」にしていないためズレている。

と様々です。しかし、何をどうやっても解決できませんでしが、以下の方法で解決できました。

それは『INDEX関数』と『MATCH関数』を組み合わせる方法です。これを使えば、VLOOKUPの作法から解放されます。

INDEX関数とMATCH関数を使ったサンプル

詳しい説明は省きます。

エクセル VLOOKUPとINDEX+MATCH関数のサンプル

上記と同じエクセルのサンプルをダウンロードしてお使いください。

sample.xlsx（12.7 kbyte）

VLOOKUPで値を参照する例

IDから型番を抽出

これは正常に値を参照できます。 

=VLOOKUP(F5,$A$2:$D$5,3,FALSE)

型番からIDを抽出

これは#N/Aエラーになります。 

=VLOOKUP(F10,$A$2:$D$5,-2,FALSE)

 

INDEX+MATCHで値を参照する例

IDから型番を抽出

これもVLOOKUP同様に正常に値を参照できます。 

=INDEX($A$2:$D$5,MATCH(F16,$A$2:$A$5,0),3)

型番からIDを抽出

こちらはVLOOKUPで失敗したことが正常に値を参照できます。 

=INDEX($A$2:$D$5,MATCH(F21,$C$2:$C$5,0),1)

 

#N/Aを表示したくないとき

検索したい値が一覧に無い場合も#N/Aエラーになりますが、その際に空白を表示したい場合、以下2つの方法があります。ISERRORとIFERRORは一文字違いで紛らわしいので注意。

IF+ISERRORを使う方法

IF文とISERROR関数を使って以下のようにすればOKです。

=IF(ISERROR(INDEX($A$2:$D$5,MATCH(F21,$C$2:$C$5,0),1)),"",INDEX($A$2:$D$5,MATCH(F21,$C$2:$C$5,0),1))

IFERRORを使う方法

IFERROR関数を使う方法もあります。こちらの方が簡単ですね。

=IFERROR(INDEX($A$2:$D$5,MATCH(F21,$C$2:$C$5,0),1),"")

Microsoft 365またはOffice 2021ならXLOOKUPが便利

バージョンの新しいエクセルならVLOOKUPの弱点を克服したようなXLOOKUPが使えます。またHLOOKUPの機能もカバーしているので、縦横のどちらの方向の参照もできます。常に決まったバージョンや新しいバージョンが使える環境ならば、こちらの方が簡単と思います。