VLOOKUP関数を学んでエクセルスキルを上げましょう！【エクセル】エラーの原因と対策も詳しく説明します

「エクセルでVLOOKUP関数を使いこなせるようになりたい」
「VLOOKUP関数とIF関数を組み合わせてより便利にしたいんだけど…」VLOOKUP関数は、IF関数やSUM関数と並んで「よく使われる関数」上位にランキングされるものの一つです。
しかし使う際には条件や注意しなければならないこともあって、始めは簡単ではないかもしれません。

ここではそんなVLOOKUP関数の基本や応用テクニック、エラー時の原因と対応方法について解説します。
これであなたもVLOOKUP関数を使いこなせるようになりますよ！

1.VLOOKUP関数の使い方

では始めに「VLOOKUP関数の基本的な使い方」や「列番号の自動取得の仕方」、「０ではなく空白で表示する方法」と「含む条件の指定の仕方」をみてみましょう。

1-1.VLOOKUP関数の基本

まずはVLOOKUP関数の概要・使い方についてです。

1-1-1.VLOOKUP関数ってどんな関数？

VLOOKUP関数とは、「指定された１列目で特定の値を検索し、指定した列と同じ行にある値を返す」とても便利な関数です。
では具体的な例を見てみましょう。

セルF2に氏名を入力した時、左の表をもとにその人の受講日をセルF5に表示させたい時、VLOOKUP関数は有効です。検索値とデータの範囲、欲しいデータの列番号などを指定すれば、検索値がある行の任意の列にある値を返してくれます。

このように、元になる表から必要な情報だけを取り出せるのがVLOOKUP関数なのです。

1-1-2.VLOOKUP関数の構文

ではVLOOKUP関数ではどのようなことを指定すればいいのでしょうか？
VLOOKUP関数の構文は次の通りです。

では数式を作ってみましょう。

（１）数式バーに「=VLOOKUP(」と入力しfxボタンを押す

（２）関数の引数ウィンドウで検索値、範囲（元の表の範囲）、列番号（範囲の内での左からの列番号）、検索方法（完全一致はFALSE）を指定する

※範囲の1列目は検索する列とすること

（３）完了

なお、VLOOKUP関数を使う時には、「範囲の１列目を検索列とする」ことに注意しましょう。
範囲の１列目が検索値を検索する列になっていないとエラーとなってしまいます。

1-2.列番号を自動移動させる方法

VLOOKUP関数は、「検索値」と一致した行のデータをまるっと抜き出すこともできます。

つまり上の図のように、セルF2に入力した「5」を検索値として№5の氏名や出身県、受講日などのデータを全て、VLOOKUP関数を使って抜き出すことができるということです。

VLOOKUP関数の引数において検索値はF5、範囲はA2:D7、検索方法はFALSEとし、列番号を「氏名の表示セル」では2、「出身県の表示セル」では3、「受講日の表示セル」では4とすればよいのです。

でも全部のセルに手入力で数式を入れるのは大変ですよね。そこで使えるのが「COLUMNS関数」です。
COLUMNS関数は「配列または参照の列数を返す」関数で、簡単に言うとカッコ内で指定した範囲の列数を返してくれます。

例えば「=COLUMNS(A1:B1)」は、カッコ内でA列からB列までの2列が指定されているので「2」と返ってきます。

つまりVLOOKUP関数の引数である列番号に上手くCOLUMNS関数を使えば、後は数式をコピーするだけで2、3、4と変わってくれるのです。

例えばセルG2に入れる数式は「=VLOOKUP($F$2,$A$2:$D$7,COLUMNS($A$1:B$1),FALSE)」となります。
COLUMNS関数の結果は「2」となるので、№５の人の氏名が返ってくるのです。
ちなみに「$」マークは、付けると数式をコピーしても行や列を固定してくれるものになっています。

次にこの数式をセルH2にコピーすると、「=VLOOKUP($F$2,$A$2:$D$7,COLUMNS($A$1:C$1),FALSE)」と、COLUMNS関数のカッコ内だけが変わります。
これによりCOLUMNS関数の結果は「3」となって、№５の人の出身県が返ってきます。

このようにVLOOKUP関数とCOLUMNS関数を組み合わせれば、行のデータを抜き出すときにとても楽になるのです。

1-3.0ではなく空白で表示させるには？

次にVLOOKUP関数の結果を、０ではなく空白で表示させる方法についてです。

VLOOKUP関数を使った時の困りごととして、上の例のように「返す値が空白の時には０になる」ことが挙げられます。
これはVLOOKUP関数の仕様なのである意味仕方ないのですが、これにも対処法はあります。

それはVLOOKUP関数の数式の後に「&””」を付けることです。

このようにすれば０ではなく空白が表示されるようになります。

1-4.特定の文字列を含む条件の指定

続いて特定の文字列を含む条件の指定の仕方です。

例えば「あの人名前何だっけなぁ？」といった時、上の例のように思い浮かんだ名字や名前だけで検索できると楽ですよね。
このようにVLOOKUP関数は特定の文字列を含む条件で検索することもできます。

数式のポイントは検索値の所で、「値の中に特定の文字列が含まれている」としたい時には上の例のように検索値の前後に＆でつなげた「“*”」を付けます。

*（アスタリスク）は「ワイルドカード」と呼ばれ、これを後ろに付ければ「文字列の頭に特定の文字がある」、前に付ければ「文字列の最後に特定の文字がある」、前後に付ければ「特定の文字列が含まれている」という条件にすることができます。是非試して見てくださいね。

2.VLOOKUP関数応用編！もっと複雑な使い方

次にVLOOKUP関数のもっと複雑な使い方を紹介します。
複数条件の指定の仕方やIF関数の組み合わせ方を見てみましょう。

2-1.複数条件の指定の仕方

まずは複数条件の指定の仕方からです。

通常VLOOKUP関数では一つの条件しか指定できません。
しかし工夫すれば複数条件にすることもできるのです。

例えば上のような表があったとします。
ここでは販売先と品名を検索条件として、該当する行の数量をセルH2に表示させてみます。

（１）左端に列を挿入し、セルA2に「=B2&C2」と入力する

（２）セルA3～A6までに数式をコピーする

（３）セルH2に「=VLOOKUP(F2&F5,A2:D6,4,FALSE)」と入力すれば完了

このように、検索用に値を＆でつなげたものを範囲の左端に用意し、VLOOKUP関数で指定する検索値も＆でつなげたものにすることがポイントです。
このようにすれば複数条件での検索が可能で、条件を3つ、4つと増やすこともできます。

2-2.IF関数と組み合わせる

次にVLOOKUP関数とIF関数の組み合わせ方です。

例えば上のような表があり、セルD2で氏名を指定するとセルE2に合否の判定が表示されるようにしたい時には、セルE2に「=IF(VLOOKUP(D2,A2:B7,2,FALSE)>=60,"合格","不合格")」と入力します。

なお、IF関数の構文は次の通りです。

この数式は、VLOOKUP関数で氏名を検索値としてその人の得点を抽出し、IF関数で合格基準点に照らし合わせて合否を表示させており、具体的にはVLOOKUP関数の結果「69」が返され、IF関数で60以上のため真となり、結果「合格」が表示されたのです。

このようにVLOOKUP関数とIF関数を組み合わせればさらにやれることの幅は広がります。
是非この組み合わせも使えるようになってくださいね。

3.VLOOKUP関数のエラー原因と対処法

最後にVLOOKUP関数がエラーになる原因と対処法について確認します。
エラー毎に見てみましょう。

3-1.「#REF!」エラーになる場合

まずは「#REF!」エラーになる原因と対処法からです。

3-1-1.列番号が範囲を超えている

この場合、まず「列番号が範囲を超えている」ことが考えられます。

例えば上の表からセルF2に指定した人の受講日をセルF5に表示させようとしてVLOOKUP関数を使いましたが「#REF!」エラーとなりました。

エラーとなった原因はVLOOKUP関数で指定した「範囲」がB列～D列と3列なのに対し、「列番号」を4としており、範囲を超えているためです。

つまりこの例では、「列番号」を「4」ではなく「3」とすれば正しく表示されます。

#REF!エラーが出た時には、「範囲」と「列番号」が正しいかを確認しましょう。

3-2.「#N/A」エラーになる場合

次に「#N/A」エラーになる原因と対処法についてです。

3-2-1.検索値と検索列で半角/全角が一致していない

この場合には、「検索値と検索列で半角/全角が一致していない」ことが考えられます。

例えば上の例は「佐藤　幸三」さんを検索していますが、セルB3にいるにもかかわらず、結果はエラーとなっています。

この原因は「苗字と名前の間の空白」で、検索列（B2:B5）は全角スペースが入っているのに検索値（F2）は半角スペースとなっているからです。

つまりこの例では、検索値を全角スペースとすれば正しく表示されます。

#N/Aエラーが出た時にはまず、検索値と検索列で一致するものがあるかを確認しましょう。

3-2-2.左端の列が検索列ではない

次に「左端の列が検索列ではない」ことも考えられます。

上の例でも「佐藤　幸三」さんを検索していますが、なぜかエラーとなっています。

この原因は「範囲で指定した左端の列が検索列ではない」ことです。

つまりこの例では、「範囲」を「$A$2:$D$7」ではなく「$B$2:$D$7」にし、「列番号」を「4」から「3」にすることで正しく表示されます。

#N/Aエラーが出た時には「範囲の左端の列が検索列となっている」かも確認しましょう。

4.まとめ

以上VLOOKUP関数について、基本的な使い方から応用編、さらにエラーの原因と対処法について解説しました。

VLOOKUP関数はとても使える関数で、活躍する場面は多々あります。
ここで紹介した内容を覚えれば今後資料を作る時などに大いに役立つと思いますので、何度も確認してくださいね！