VLOOKUP関数が上手く動かない時に確認するポイントをまとめました
社内の情報システム部門にいるシステムエンジニアの方は、ExcelでVLOOKUPという関数を使う機会があると思います。そんなときに、上手くいかない時がよくあると思います。私もよく職場の仲間から上手くいかないと相談されます。
このページでは、VLOOKUPが上手く動かない時にチェックするポイントを紹介します。
VLOOKUP関数を使った例
例として、注文一覧という画面から商品コードを入力したら、別シートの商品マスタから商品名と価格を取得してくる画面を作ってみました。
◆商品マスタ
Ａ列：商品コード
Ｂ列：商品名
Ｃ列：価格
★注文一覧画面はこんな感じです。
★VLOOKUPの動き
左の注文画面から商品コード（赤枠）を入力すると、右の商品マスタから商品名と価格を取得してくるようにＦ列とＧ列にVLOOKUP関数が入っています。
Ｆ列のセルには、以下のような関数が入っています。
=VLOOKUP(E2, 商品マスタ!$A$2:$C$8 ,2 ,0 )
＜引数の説明＞
引数１：検索する値
商品マスタを検索するためのキーとなる値が入ります。
この例では、Ｅ２は商品コードが入っています。（赤枠）
引数２：検索する範囲
ここには検索する範囲を XX:XX として指定します。
普通は別シートにしますので、シート名!XX:XX という書き方になります。
この例では 商品マスタ!A2:C8 としています。
検索する範囲は、A2:C8 となります。
引数３：検索結果を返す列番号
検索する範囲の何番目の列の値を返すかを指定します。
左から１、２，３とかぞえます。
１はキー値となりますので、2以降の数字を指定することになります。
この例では、２番目の商品名を返すので２を指定しています。
引数４：検索方法
０：完全一致したときに値を返す意味。
上手く動かない時の確認ポイント
上手く動かない時は、次の点を確認してみてください。
＜確認ポイント①＞
検索する範囲の一番左の列に、キー値がくるように検索範囲を指定します。
ここでは、商品マスタの一番左の列が商品コードです。
＜確認ポイント②＞
注文一覧のキー値と商品マスタのキー値となるセルの表示形式を合わせます。
この例では、商品コードを数値にしています。
上手く動かない時は、表示形式が違っていることが多いです。
＜確認ポイント③＞
商品マスタは商品コードで昇順にソートされている必要があります。
＜確認ポイント④＞
引数２に指定している「商品マスタ!$A$2:$C$8」の中の＄は、絶対値の意味です。
この＄マークを付けないと、セルをコピーしたときに、EXCELは勝手にA2,A3,A4と値を変えてしまいます。
これが便利な時もありますが、勝手に変えられると不都合のときもあります。
このVLOOKUP関数が入っているセルをコピーしたときに、A2,A3,A4と値が変わってしまってはその都度手で修正しなくてはなりません。
そのため、この＄マークをアルファベットと数値の前に入れています。
こうすることで、このセルをどこにコピーしてもA2:C8という検索範囲は変わりません。
エラーになったときに、「#N/A」という表記をださないようにするには
エラーになったときに、「#N/A」という表記をださないようにするには、以下のように記述します。
=IFERROR(VLOOKUP((E2, 商品マスタ!$A$2:$C$8 ,2 ,0 ),"")
これで、たとえば商品マスタに該当の商品コードが無かった時でも、「#N/A」は表示されません。
別なブックを検索範囲とするとき
検索範囲を別なEXCELファイルに指定することもできます。
下の例では、商品マスタ.xlsxというEXCELファイルの商品マスタというシートを参照するように指定しています。
=VLOOKUP(E3,[商品マスタ.xlsx]商品マスタ!$A$1:$C$8,2,0)
こうすれば、マスタは１ファイルで管理できるのでとても便利です。
以上ですが、この記事が参考になれば幸いです。
他にもいろいろ目次ページに役立つ記事を書いています。