Excelで特定の文字に対応する値を表から抽出したい【VLOOKUP関数】

この記事では、ExcelのVLOOKUP関数の使い方を画像つきで解説しています。

この記事で紹介する操作は、以下のOS/Excelに対応しています。

WindowsXP
WindowsVista
Windows7
Windows8
Windows10
Windows11
Excel97
Excel2000
Excel2002
Excel2003
Excel2007
Excel2010
Excel2013
Excel2016
Excel2019
Excel2021

VLOOKUP関数では、入力する値に一致するセルを別の表から検索し、そのセルと同じ行の指定した列の情報を抽出することができます。

今回のサンプルでは、 商品名を入力したら、単価表からその商品名を探して隣の列にある単価を抽出します。ゴールは以下の画像のようになります。

最終目標
VLOOKUP関数の使い方
VLOOKUP関数の使い方

記事内では、もっとシンプルな表を使用して解説するので、同じ表を作成して一緒に操作してみましょう。

VLOOKUP関数の使い方【わかりやすく画像で解説】

VLOOKUP関数で単価を返す表と、VLOOKUP関数で検索する対象になる表を作成して解説します。

見た目でわかりやすくするため、必要最低限の書式(罫線や背景色)を設定していますが、書式がなくても関数は動作するので書式なしのベタ打ちでもOKです。

1. VLOOKUP関数で使用するサンプル表を作成する

A列は商品名を入力する列、B列はVLOOKUP関数で単価を返す列、D列~E列はあらかじめ商品名と単価を入力した表を作成します。

特定の値を検索して指定した列の情報を抽出する/VLOOKUP関数の使い方
VLOOKUP関数の使い方

2. [B2]セルを選択し、数式バーの[fx]ボタンをクリック

VLOOKUP関数を入力していきます。[B2]セルを選択し、数式バーの[fx]ボタン(関数の挿入ボタン)をクリックしてください。

特定の値を検索して指定した列の情報を抽出する/VLOOKUP関数の使い方
VLOOKUP関数の使い方

3. [関数の挿入]ボックスでVLOOKUP関数を選択

[関数の挿入]ダイアログボックスが表示されます。

画面上の[関数の検索]枠内に半角で「vl」と入力し[検索開始]ボタンをクリックします。そうすると画面中央の[関数名]枠内に「VLOOKUP」が表示されるので、[OK]ボタンをクリックします。

数式を入力する際は、必ず半角英数で入力するようにしましょう。全角で入力すると正しい結果を出せない場合があります。
特定の値を検索して指定した列の情報を抽出する/VLOOKUP関数の使い方
VLOOKUP関数の使い方
[関数の挿入]ダイアログボックスは日本語でも検索ができますが、Googleなどの検索エンジンと比較すると検索精度が低いことが難点です。そのため、利用する関数名がわかっている場合は、関数の頭文字で検索した方が効率的です。

4. [関数の引数]ボックスで引数を入力

[関数の引数]ダイアログボックスが表示されるので、[検索値][範囲][列番号][検索方法]の4つの引数を入力します。[検索値]と[範囲]はクリックやドラッグで指定するとセル番地を間違えることが少なくなります。

[検索値] [検索値]枠内をクリックしてカーソルを表示した状態で、[A2]セルをクリック。
[範囲] [範囲]枠内をクリックしてカーソルを表示した状態で、[D1]から[E4]セルをドラッグして選択。「D1:E4」と表示されたらそのまま[F4]キーを1回押して「$D$1:$E$4」に変更。セル番地にドルマークをつけると絶対参照になり、数式をコピーしてもセル番地が固定されたままになる。
[列番号] [列番号]枠内をクリックしてカーソルを表示した状態で、キーボードから「2」を入力。[範囲]の1列目から[検索値]に一致するセルを検索し、そのセルの2列目を返すという意味になる。
[検索方法] [検索方法]枠内をクリックしてカーソルを表示した状態で、キーボードから「0」を入力。「0(FALSE)」は完全一致検索、「1(TRUE)」は近似一致検索を指定する。
特定の値を検索して指定した列の情報を抽出する/VLOOKUP関数の使い方
[]VLOOKUP関数の使い方
[範囲]を絶対参照で指定するのは、数式をコピーするときに範囲がずれてしまわないようにするためです。相対参照のままだと、数式を下にコピーすると範囲も下に下にとずれていきます。結果、範囲内に検索値が見つからずエラー値が返ることになります。

5. 入力した数式をコピー

A列に商品名を入力していないため、VLOOKUP関数の結果は「#N/A」というエラー値で返りますが、正常な動作なので安心してください。

#N/Aエラー値は、「指定した範囲内に検索値が見つかりません。」というエラーです。VLOOKUP関数の[範囲]として指定した1列目の[D1]セルから[D4]セルには空白のセルはないために #N/Aエラー値が返ります。

#N/Aエラー値のままでかまいませんので、[B7]セルまで数式をコピーします。

[B2]セルを選択して状態で表示される右下のフィルハンドル(小さな四角形)を[B7]セルまでドラッグしましょう。

特定の値を検索して指定した列の情報を抽出する/VLOOKUP関数の使い方
VLOOKUP関数の使い方
特定の値を検索して指定した列の情報を抽出する/VLOOKUP関数の使い方
VLOOKUP関数の使い方

6. A列に商品名を入力してVLOOKUP関数の動作を確認

[A2]セルから[A7]セルまで商品名を入力して、VLOOKUP関数の動作を確認してみましょう。

ためしに、[範囲]にある「みかん」「りんご」「いちご」以外を入力すると、見つからないために #N/Aエラー値 が返ることも確認してみてください。

特定の値を検索して指定した列の情報を抽出する/
VLOOKUP関数の使い方

VLOOKUP関数の一番簡単なつくり方は以上です。数式の作成に慣れてきたら、[関数の挿入]ダイアログボックスを使わずに、セルに直接数式を入力した方が効率的です。

VLOOKUP関数の構造

VLOOKUP関数の基本的なつくり方がわかったところで、続いては構造を理解していきましょう。

VLOOKUP関数には4つの引数があります。

VLOOKUP関数の引数
VLOOKUP([検索値],[範囲],[行番号],[検索方法])
[検索値] [範囲]に指定した1列目から探す値。検索値を入力したセル番地を指定する方法が一般的。数式内で直接入力する場合は、ダブルクォーテーション(半角の”)でくくる。
[範囲] [検索値]を検索する範囲を指定する。[範囲]に指定した1列目から[検索値]を探し、その[検索値]がある行の指定した列を返すため、[範囲]は検索値を含む列から始まり、指定する[列番号]がある列を含む必要がある。
[列番号] [範囲]の1列目から[検索値]に一致するセルを検索し、そのセルがある行の何列目を返すかを指定する。半角数字で直接入力するか、列番号を入力したセル番地を指定する。
[検索方法] 「0(FALSE)」は完全一致検索、「1(TRUE)」は近似一致検索を指定する。0を指定すると半角全角を含め完全に一致する情報を検索する。1を指定すると検索値に一番近い文字コードを検索する。

このVLOOKUP関数の構造を、今回のサンプルに当てはめてみましょう。

作成した数式
B2=VLOOKUP(A2,$D$1:$E$4,2,0)
[検索値] A2
[範囲] $D$1:$E$4
[列番号] 2
[検索方法] 0
特定の値を検索して指定した列の情報を抽出する/
VLOOKUP関数の使い方

この数式を文章化するとよりわかりやすくなります。

数式を文章化

B2セルのVLOOKUP関数は、A2セルの「いちご」を検索範囲の1列目D1セルからD4セルから完全一致で検索し、見つかったらそのセルと同じ行の2列目を返す。

今回は「なんとなくわかったかも…」程度でも大丈夫!繰り返し実践で利用していけば使いこなせるようになります!

実務で利用したときに正しい結果が出ない場合は、[検索値]に若干の違いがあったり、[範囲]がずれてしまっていたりなど、複数の原因が考えられます。

困ったときには以下を参考にしてみてください。

VLOOKUP関数でよくある質問

最後に、VLOOKUP関数でよくある質問やよくあるトラブルをまとめます。

[検索値]と同じ文字(数字)が検索範囲の表にあるのに#N/Aエラーになる

検索値に設定した文字や数値が、検索範囲にあるのに結果が間違っている場合は、検索値が一致していないか、検索範囲が間違っている可能性があります。

【近日公開予定】VLOOKUP関数の結果が正しくない①[検索値]と同じ文字(数字)が検索範囲の表にあるのに#N/Aエラーになる

エラーではなく間違った結果が表示される

検索値とは異なる別の行が抽出されてしまう場合、[検索条件]の指定が間違っている可能性があります。

【近日公開予定】VLOOKUP関数の結果が正しくない②エラーではなく間違った結果が表示される

1 COMMENT

YAMA&KAWA

解決しました!
どのサイトよりも分かりやすかったです。
これからもいろいろUPお願いします。

返信する

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください