VLOOKUP関数の使い方(データを検索する):Excel関数

Excelでよく使われる便利なVLOOKUP関数の使い方を具体例を使って説明しています。
ちょっと戸惑う引数の指定方法も丁寧に分かりやすく書いています。
また、VLOOKUP関数単体ではなく他の関数MATCH関数やINDIRECT関数などと組み合わせて利用する方法もあります。

スポンサードリンク

2021/12/26

完全一致を検索する(検索の型:FALSE)	エラー処理(#N/Aを表示しない)
近似一致を検索する(検索の型：TRUE)	検索範囲に名前を定義する場合
	検索範囲にテーブルを利用する方法
検索表を使用しない方法	#N/Aエラーが返されるいろいろなケース
セルのデータ型を調べる	VLOOKUP関数で複数条件の検索をする
VLOOKUP関数とMATCH関数の複合

はじめに：VLOOKUP関数のポイント

データリスト範囲の左端列を検索して同じ行の指定列の位置の値を返します。
検索する列が昇順に並んでいないときは検索の型をFALSEとして完全一致で検索します。
検索する列が昇順に並んでいるときは検索の型でTRUE(もしくは省略してもよい)を使用します。
検索値が見つからない場合はエラー値#N/Aが返されます。(#N/Aエラーを表示しない方法はエラー処理をご覧ください)
Microsoft 365やExcel2021をお使いの方は、"より強力な新しい関数" XLOOKUP関数を使うことができます。
XLOOKUP関数で範囲や配列を検索する:Excel関数　をご覧ください。

　ブイルックアップ
=VLOOKUP(検索値,範囲,列位置,検索の型)
　　　検索の型：「TRUE」もしくは[省略]（「1」と入力してもOK)
　　　　　　　　検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。
　　　　　　　　(注)検索範囲の値は昇順に並んでおく必要があります。
　　　検索の型：「FALSE」(「0」と入力してもOK)
　　　　　　　　検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。
　　　　　　　　文字列の検索に適します。

[A]　完全一致で検索する　(検索の型：FALSE)

検索の型でFALSEを指定すると、完全一致で検索しますので、検索値が昇順に並んでいないときでも検索が可能です。
引数の検索の型の入力は FALSE の代わりに 0 を入力してもOKです。

【問題1】商品一覧表（H3:J6)があります。
C4:D6セルに数式を入れて、見積書の品番を入力すると商品名と単価が自動的に入力されるようにしなさい。

	B	C	D	E	F	G	H	I	J
2		見積書					商品一覧表
3	品番	商品名	単価	数量	小計		品番	商品名	単価
4	R01			1	5,000		T01	テレビ	50,000
5	T01			2	40,000		R01	ラジオ	5,000
6	V01			3	150,000		V01	ビデオデッキ	20,000
7				合計	195,000

問題1の解答例

検索する列の品番 H4:H6 は昇順に並んでいません。よって、検索の型は FALSE として、完全一致で検索します。
検索するデータリストの範囲は H4:J6 です。C4セルに入力する数式は、下のセルにもコピーしますので $H$4:$J$6 と絶対参照とします。
返す商品名は2列目にあるので、列番号は 2 となります。
C4セルに入力する数式は、
=VLOOKUP(B4,$H$4:$J$6,2,FALSE)
または
=VLOOKUP(B4,$H$4:$J$6,2,0)
となります。
「B4セルの『B01』を範囲『H4:J6』の左端の列から探し、完全に一致した値が有ったら2列目の値を返す」という意味になります。
C4セルに数式が入力できたら、下方向へ数式をコピーします。
関数の引数で数式を作成する場合
- 検索値：品番の入力されているB4セルになります。
  範囲：商品一覧のデータ部分H4:J6を絶対参照で指定します。
  （範囲を絶対参照にするのはC4セルの数式をC5,C6セルにコピーする時値が変化しないようにするためです。）
  列位置：範囲の2列目ですので「2」とします。
  検索の型：文字の検索や固有品番などの検索では[FALSE] にします。[FALSE]のかわりに[0]でもOK。
D5セルの数式は、単価は 3 列目にあるので、列番号は 3 とします。
=VLOOKUP(B4,$H$4:$J$6,3,FALSE)
または
=VLOOKUP(B4,$H$4:$J$6,3,0)となります。

数式を1つにまとめる
C4セルの数式をD4セルでも利用できるように、引数の列番号を =VLOOKUP($B5,$H$4:$J$6,COLUMN(B2),FALSE) とすることもできます。
- (説明)
  C4セルの列位置「2」を、D4セルでは「3」になるように細工をします。
  COLUMN関数を使うと、右方向へコピーして値が1増加するので「2」を返すように「COLUMN(B1)」とします。
  この場合重要なのは「B」=「2」なのでCOLUMN(B100)でも良く、「B1」としたのは見た目だけ？のことです。
C4セルのオートフィルボタンを右方向へドラッグして、数式をコピーします

↓
さらに、D4セルのオートフィルボタンを下方向へドラッグして、数式をコピーして完成です。
Spill(スピル)が利用できる場合(Microsoft365、Excel2021)
C4セルに=VLOOKUP($B$4:$B$6,$H$4:$J$6,COLUMN(B1),FALSE) と入力して、D4セルにコピーします。
検索値がセル範囲になっているところに注目です。

VLOOKUP関数のエラー処理(#N/Aを表示しない)　　　　topへ

検索値が検索する範囲にないときはエラー#N/Aが表示されます。
#N/Aが表示されないようにするにはいろいろな方法があります。
Excel2007以降、Excel2013以降ではIFERROR関数やIFNA関数が使えて簡潔な数式で処理ができるようになりました。

IFERROR関数でエラー処理をする(Excel2007以降で使用できます)

詳細は　IFERROR関数　をご覧ください。
C4セルの例　=IFERROR(VLOOKUP(B4,$H$4:$J$6,2,FALSE),"") 　とします。
エラー #N/A　は表示されず、空欄に見えます。「""(0文字の文字列)が表示されます。」

IFNA関数でエラー処理をする(Excel2013以降で使用できます)

詳細は　IFNA関数　をご覧ください。
C4セルの例　=IFNA(VLOOKUP(B4,$H$4:$J$6,2,FALSE),"") 　とします。

ISNA関数やISERROR関数でエラー処理をする

VLOOKUP関数がエラーとなるか否かを調べてIF関数で対処します。
エラーの表示を無くするには　IF関数とISNA関数を使います。
C4セルの例：=IF(ISNA(VLOOKUP(B4,$H$4:$J$6,2,FALSE)),"",VLOOKUP(B4,$H$4:$J$6,2,FALSE))
ISERROR関数でもOKです。
=IF(ISERROR(VLOOKUP(B4,$H$4:$J$6,2,FALSE)),"",VLOOKUP(B4,$H$4:$J$6,2,FALSE))

COUNTIF関数で処理する

検索値が検索範囲にあるか否かをCOUNTIF関数で調べてIF関数で対処します。
=IF(COUNTIF($H$4:$H$6,B7)=0,"",VLOOKUP(B7,$H$4:$J$6,2,FALSE))
なお、Excelでは1以上の数値はTrueと解釈されるので、以下のように書くこともできます。
=IF(COUNTIF($H$4:$H$6,B7),VLOOKUP(B7,$H$4:$J$6,2,FALSE),"")

VLOOKUP関数の検索範囲にテーブルを利用する　　　　 topへ

商品一覧表に商品がどんどん追加されると、数式のセル範囲を変更しなければいけません。
この手間を避けるにはテーブル機能を利用します。(Excel2007以降にテーブル機能が追加されました。)
セル範囲をテーブルにすることで、テーブルにデータが追加されたケースなどにそのまま対応できます。
数式の変更の必要がありません。
- 商品一覧表をテーブルに変換して、テーブル名を商品コード　としました。
  すると、C4セルの数式は　=VLOOKUP(B4,商品コード,2,FALSE)　とすることができます。
  D4セルの数式は　=VLOOKUP(B4,商品コード,3,FALSE)　となります。　
↓
メリットはテーブルのデータが増えても何ら変更が必要ないということです。
VLOOKUP関数の列位置を 2 とか 3 と指定していますが、列見出しの「商品名」「単価」を利用する方法もあります。
C4セルの数式は　=VLOOKUP($B4,商品一覧表,MATCH(C$3,商品一覧表[#見出し],0),FALSE)　とします。
列見出しの位置を　テーブルの見出し行のどこにあるかを MATCH関数で求めています。
この数式を D4セルへコピーすればよいことになります。

VLOOKUP関数の検索範囲に定義した名前を利用する　　　　 topへ

数式の可読性を上げるのにはセル範囲に名前を定義すると、数式がわかりやすくなります。

検索範囲に名前を定義して、数式にその名前を使用すると数式が読みやすくなります。
(数式を見て、すぐにその名前の範囲が参照されているのがわかります。)
セル範囲 H4:J6 に『商品一覧表』と名前を定義します。
（ポイント：検索列が一番左になるようにセル範囲を選択して、名前を定義します。）
1. 名前ボックスを使って名前を定義する方法
  1. セル範囲 H4:J6 をドラッグして選択します。
  2. 「名前ボックス」に『商品一覧表』と入力し、　[Enter] キーを押します。
    これで、セル範囲に名前が定義されます。
2. リボンから名前を定義する方法
  1. セル範囲 H4:J6 をドラッグして選択します。
  2. Excel2007以降は、[数式]タブの[名前の定義]を実行します。
    Excel2003以前は、メニューバーの［挿入]→[名前]→[定義]を選択します。
  3. 「名前の定義」ダイアログの名前に『商品一覧表』と入力し、[OK]ボタンをクリックします。
    これで、セル範囲に名前が定義されます。
C4セルの数式は　=VLOOKUP(B4,商品一覧表,2,FALSE)
D4セルの数式は　=VLOOKUP(B4,商品一覧表,3,FALSE)　とします。
- 列位置をCOLUMN関数で置き換え、C4セル=VLOOKUP(B4,商品一覧表,COLUMN(B1),FALSE)とすれば、D4セルへコピーすればOK。
C4:D4セルを選択し、オートフィルでC6:D6まで数式をコピーして完成です。

[B]　近似一致を検索する場合　(検索の型：TRUE)　　　　　topへ
　　　　(注)検索値が昇順に並んでいる必要があります。近似値(検索値未満の最大値)を範囲の中から探します。
　　　　　　検索値が数値の範囲ではなく、その値に対応する時は[FALSE]とします。

検索値が数値で昇順に並んでいるときは、近似値(検索値未満の最大値)を範囲の中から探すことができます。

【問題2】成績の評価を付けたいと思います。右の評価一覧表（G4:J6)にしたがって、D4:E6セルに数式を入力しなさい。
なお、G4セルは0以上、G5セルは40以上、G6セルは70以上を表現しています。

	B	C	D	E	F	G	H	I	J
2							評価一覧表
3	名前	得点	評価A	評価B		得点		評価A	評価B
4	今田	50	△	良		0	以上	×	不可
5	河野	20	×	不可		40	以上	△	良
6	山田	70	○	優		70	以上	○	優

問題2の解答例-1　

問題1の解答例とほとんど同じですが、数値の範囲での検索になりますので、検索の型を[TRUE]または「1」もしくは[省略]します。
検索の型が「TRUE」のとき、検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。つまり、○○以上の○○が一致する値として返されます。
- 検索の型を省略：=VLOOKUP(C4,$G$4:$J$6,3)
  検索の型をTRUE：=VLOOKUP(C4,$G$4:$J$6,3,TRUE)
  検索の型を1 　：=VLOOKUP(C4,$G$4:$J$6,3,1)
  上の3つは同じ結果になります。
この場合、得点欄の値以上の意味になります。得点欄（G列)は昇順に並んでいることが必要です。
- 「検索の型を[TRUE]または「1」もしくは[省略]します」と書いていますが、実は1以外(0を除く)の数値を使用してもエラーとならずにTRUEと同様に計算されます。
  Excelのワークシート関数ではFalse=0、それ以外はTrueとみなされるためです。
  ただし、=TRUE*1　といった計算を行うとワークシートでは「1」が返されますので、「1」が使われる理由と考えます。
  なお、VBAでは　Trueは「-1」と異なった処理がされますので注意が必要です。

問題2の解答例-2　検索範囲に名前を定義して使う方法

セル範囲 G4:J6 に『評価一覧』と名前を定義すると、
- D4セルの数式は =VLOOKUP(C4,評価一覧,3)
  E4セルの数式は =VLOOKUP(C4,評価一覧,4)
となります。
D4:E4 セルを選択し、オートフィルでD6:E6セルまで数式をコピーして完成です。
名前の定義方法：G4:J6を選択し、名前ボックスに「評価一覧」と入力します。
別解：D4セル=VLOOKUP(C4,評価一覧,COLUMN(C1))　として、E4セルへコピーしてもOKです。

[C]　検索表を使用しない方法　　(数式中に検索範囲のデータを書く方法)　　　　topへ

(解答例2-3)　シートに検索表を作成しない方法
- 数式内に対応する値を並べて記述します。
  D4セルの数式　=VLOOKUP(C4,{0,"×","不可";40,"△","良";70,"○","優"},2)
  下表のデータを{0,"×","不可";40,"△","良";70,"○","優"}で表現しています。
  行の区切りは , (コロンまたはカンマ)、列の区切りは　; (セミコロン)とします。
  
  0 × 不可
  
  40 △ 良
  
  70 ○ 優
  
  E4セルの数式　=VLOOKUP(C4,{0,"×","不可";40,"△","良";70,"○","優"},3)
  D4:E4 セルを選択し、オートフィルでD6:E6セルまで数式をコピーして完成です。

[D]　検索値が見つからず #N/Aエラーが返されるいろいろなケース　　　 topへ

シート上で検索値が検索範囲内の値と同じ値が入力されているように見えているのに、エラーとなるときがあります。

検索値にスペースが紛れ込んでいないか確認してみます。特に最後にスペースが含まれる場合は気づきにくいです。
1. [ホーム]タブの[検索と選択]→[置換]を実行します。
  ショートカットキーは[Ctrl]+[H]です。
2. 検索する文字列：半角または全角スペースを入力します。(画面では見た目の変化はないです)
  置換後の文字列：何も入力しません。
  『半角と全角を区別する』にチェックは入れません。(【オプション】をクリックすると表示されます)
  【すべて検索】ボタンをクリックし、【すべて置換】をクリックすると、一度に置換されます。
  確認しながら置換するときは、【次を検索】と【置換】を使います。
他のソフトなどで作成したデータで空白文字CHAR(160)が紛れ込んでくることがあります。
- CHAR(160)はTRIM関数やCLEAN関数では取り除くことができないので、SUBSTITUTE関数で取り除きます。その後、形式を選択して貼り付けの「値」でデータを置き換えます。
  - =SUBSTITUTE(A1,CHAR(160),"")
表示形式は文字列であるが、データは数値と認識されていることがあります。
この様な場合は、=TYPE(A1)のようにTYPE関数を使ってセルのデータ形式を確認します。
- 数値データを文字列に変更したいときは、【データ】→【区切り位置】のウィザードで「文字列」を指定します。
- 文字列の数字を数値に変更したい場合は、どこかのセルに数値の「1」を入力しコピー、変更したいセルを選択して形式を選択して貼り付けで「乗算」にチェックを入れて【OK】とします。

セルのデータ型を調べる　　　topへ
　タイプ
=TYPE(データタイプ)
　　　数値は「1」、テキストは「2」、論理値は「4」
　　　エラー値は「16」、配列は「64」を返します。

C列にD列のようなそれぞれの値や数式を入力します。
C5,C6はそれぞれの結果「FALSE」「#VALUE!」が表示されます。

C7はTYPE関数の戻り値「64」が表示され、E7は戻り値「64」は数値なので、「1」が返されます。

	B	C	D	E	F
2	データタイプ	入力データ	C列の内容	TYPE関数の戻り値	E列の数式
3	数値	10	←10	1	←=TYPE(C3)
4	テキスト	文字です	←文字です	2	←=TYPE(C4)
5	論理値	FALSE	←=A1="moji"	4	←=TYPE(C5)
6	エラー	#VALUE!	←=B1/B2	16	←=TYPE(C6)
7	配列	64	←=TYPE({1,2;3,4})	1	←=TYPE(C7)

[E] VLOOKUP関数で複数条件の検索をする　　　 topへ

左端列に検索条件を1つにまとめます(作業列を利用します)。

B3セルに=C3&"_"&D3　とC3セルとD3セルの値をつないで1つにします。B８セルまで数式をコピーします。
=C3&D3 でOKな場合が多いのですが、万が一のケースを想定して区切り文字に「_」アンダーバーを使っています。
H4セルには=VLOOKUP(H2&"_"&H3,$B$3:$E$8,4,FALSE)とします。

SUMPRODUCT関数を使う事もできます。この場合B列は不要です。

上の例ではH2セルに=SUMPRODUCT((C3:C8=H2)*(D3:D8=H3),E3:E8)とします。
なお、求める値が数値である場合は上のようにSUMPRODUCT関数が使えます。
しかし、文字列を求める場合は使用できませんので、上記のように作業列を利用しVLOOKUP関数を使うか、XLOOKUP関数で対処します。

Microsoft365をお使いの場合は XLOOKUP関数で対処できます。

H4セルは　=XLOOKUP(H2&H3,C3:C8&D3:D8,E3:E8)
H5セルは　=XLOOKUP(H2&H3,C3:C8&D3:D8,F3:F8)

VLOOKUP とMATCH の複合　　　topへ
　　　縦横の検索表から、一致する値を取り出すのに、VLOOKUP関数とMATCH関数を組み合わせて使う方法です。

【問題】VLOOKUP関数とMATCH関数を使って運賃を求めなさい

	B	C	D	E
2	運賃表
3		東京	名古屋	大阪
4	鹿児島	50,000	40,000	35,000
5	宮崎	45,000	35,000	30,000
6	熊本	40,000	30,000	25,000
7	福岡	30,000	20,000	15,000
8
9	到着地	名古屋
10	出発地	熊本
11	運賃は	30,000

解答例

出発地熊本（C10セル)の行位置をVLOOKUP関数で求めます。
VLOOKUP関数は左端の列を検索するので、範囲は『B7:E7』となります。
=VLOOKUP(C10,B4:E7,○○,FALSE)
到着地の名古屋（C9セル)が何列目になるのかMATCH関数で求めます。
(注)VLOOKUP関数は検索列(一番左の列）を含む範囲になるので、MATCH関数の範囲は『B3:E3』とします。
MATCH(C9,B3:E3,0）　
この両方の式を合わせて完成です。
=VLOOKUP(C10,B4:E7,MATCH(C9,B3:E3,0),FALSE)

関連ページ
- VLOOKUP関数とCHOOSE関数を組み合わせて使う

スポンサードリンク

よねさんのWordとExcelの小部屋 » エクセル関数一覧表 » VLOOKUP関数の使い方(データを検索する)

PageViewCounter
Counter
Since2006/2/27

VLOOKUP関数の使い方(データを検索する):Excel関数

はじめに：VLOOKUP関数のポイント

[A] 完全一致で検索する (検索の型：FALSE)

問題1の解答例

数式を1つにまとめる

Spill(スピル)が利用できる場合(Microsoft365、Excel2021)

VLOOKUP関数のエラー処理(#N/Aを表示しない) topへ

IFERROR関数でエラー処理をする(Excel2007以降で使用できます)

IFNA関数でエラー処理をする(Excel2013以降で使用できます)

ISNA関数やISERROR関数でエラー処理をする

COUNTIF関数で処理する

VLOOKUP関数の検索範囲にテーブルを利用する topへ

VLOOKUP関数の検索範囲に定義した名前を利用する topへ

問題2の解答例-1

問題2の解答例-2 検索範囲に名前を定義して使う方法

[C] 検索表を使用しない方法 (数式中に検索範囲のデータを書く方法) topへ

[D] 検索値が見つからず #N/Aエラー が返されるいろいろなケース topへ

セルのデータ型を調べる topへ タイプ =TYPE(データタイプ) 数値は「1」 、テキストは「2」 、論理値は「4」 エラー値は「16」 、配列は「64」を返します。

[E] VLOOKUP関数で複数条件の検索をする topへ

左端列に検索条件を1つにまとめます(作業列を利用します)。

SUMPRODUCT関数を使う事もできます。この場合B列は不要です。

Microsoft365をお使いの場合は XLOOKUP関数で対処できます。

VLOOKUP とMATCH の複合 topへ 縦横の検索表から、一致する値を取り出すのに、VLOOKUP関数とMATCH関数を組み合わせて使う方法です。

解答例

[A]　完全一致で検索する　(検索の型：FALSE)

VLOOKUP関数のエラー処理(#N/Aを表示しない)　　　　topへ

VLOOKUP関数の検索範囲にテーブルを利用する　　　　 topへ

VLOOKUP関数の検索範囲に定義した名前を利用する　　　　 topへ

問題2の解答例-1　

問題2の解答例-2　検索範囲に名前を定義して使う方法

[C]　検索表を使用しない方法　　(数式中に検索範囲のデータを書く方法)　　　　topへ

[D]　検索値が見つからず #N/Aエラーが返されるいろいろなケース　　　 topへ

セルのデータ型を調べる　　　topへ
　タイプ
=TYPE(データタイプ)
　　　数値は「1」、テキストは「2」、論理値は「4」
　　　エラー値は「16」、配列は「64」を返します。

[E] VLOOKUP関数で複数条件の検索をする　　　 topへ

VLOOKUP とMATCH の複合　　　topへ
　　　縦横の検索表から、一致する値を取り出すのに、VLOOKUP関数とMATCH関数を組み合わせて使う方法です。