【EXCEL】VLOOKUPの#N/Aエラーで困ったらINDEX+MATCHで解決（サンプルあり）
目次
値は存在しているのに#N/Aエラーになる
いつも使っているVLOOKUPが#N/Aエラーになり、困り果てました。エラーの原因は、
- 検索値が検索範囲の左端の列にない。
- 完全一致と部分一致の指定が誤っている。
- 検索値を参照する列が昇順で並び替えられていない。
- 小数で計算した値を検索値に使用している。
- 検索範囲を「絶対参照」にしていないためズレている。
と様々です。しかし、何をどうやっても解決できませんでしが、以下の方法で解決できました。
それは『INDEX関数』と『MATCH関数』を組み合わせる方法です。これを使えば、VLOOKUPの作法から解放されます。
INDEX関数とMATCH関数を使ったサンプル
詳しい説明は省きます。
上記と同じエクセルのサンプルをダウンロードしてお使いください。
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の機能もカバーしているので、縦横のどちらの方向の参照もできます。常に決まったバージョンや新しいバージョンが使える環境ならば、こちらの方が簡単と思います。