VLOOKUP関数で検索値に一致する行のデータを取得複数結果の表示方法についても解説

VLOOKUP関数について
Excelの関数を検索する

VLOOKUP関数について

VLOOKUPの概要

検索値に一致する行のデータを検索Excel関数/検索

=VLOOKUP( 検索値 , 範囲 , 列番号 , FALSE )

概要検索値に一致する行のデータを検索して表示

範囲内の一番左の列のなかで、検索値に一致する行の列番号(範囲内の左列から)の値を表示する

A B C D E F

範囲内の左列 列番号(4)

↓

検索値に一致 → → この値を表示

　
列番号は、範囲内において何列目かを指定するのであって、Aから数えての番号ではない
列番号に1を設定すれば、検索値そのものが表示される
列番号はCOLUMN(E1)-COLUMN(B1)+1でもある
検索値のある位置を知りたいときはMATCH(行位置)と、MATCH(列位置)を用いる
単純な検索であれば ctrlを押しながらFを押して検索でよい
一番左の列データについて検索するのであって、範囲の全体を検索するわけではない
左以外としたい場合は、範囲を調整するかLOOKUP関数を用いる
TRUEとした場合、一致ではなく「超えない」データとなる(TRUE型のVLOOKUP関数)
左の列の検索対象に重複したデータがある場合は、最初のデータが表示される。VLOOKUPは商品IDの管理など、重複データを想定した関数ではないからである
データが同じに見えても表示形式が異なっていると検索の対象とならなず、エラーとなることに注意。

VLOOKUPはどのような時に使用するか

特定の商品番号についての価格を知りたい
検索語に対応する詳細データを知りたい

VLOOKUPの使い方

以下はA1～A4のIDで検索し、対応する首都名を表示します

	A	B	C	D	E
1	A1	日本	東京
2	A2	アメリカ	ワシントン	A1
3	A3	フランス	パリ	東京 =VLOOKUP( D2 , A1:C4 , 3 , FALSE ) ※もし国名で検索したいときは、 =VLOOKUP(D2,B1:C4,2,FALSE)とする
4	A4	ドイツ	ベルリン
5			VLOOKUP関数の使用を想定して最も左の列に個別に識別できるIDなどを記入しておくとよい。エラーとなる場合は、表示形式を統一してください。
6
7
8
9

VLOOKUPとCOLUMNを組み合わせる

VLOOKUP関数の列番号は、行の値を全て取り出すには列をマウスドラッグで拡張するが、数値を指定していると変化しないために値が変わらない。そのため、COLUMNを用いる。こうすることで、常に列番号は正しい値になる。
範囲については、マウスドラッグで拡張しても変化しないようにするため、$A$2:$C$4として固定している
自分で列番号を1、2と打ってももちろん問題はないし、C1に1、D1に２として、=VLOOKUP(2 , $A$2:$C$4 , C1 , FALSE) としてもよい

	A	B	C	D	E
1			2
2	1	4	2 =VLOOKUP($C$1 , $A$2:$B$4 , COLUMN(A2) , FALSE) D2の式は=VLOOKUP($C$1 , $A$2:$B$4 , COLUMN(B2) , FALSE) となっている。 COLUMNを用いず1としていると、D2でも1のままになる。範囲がAからスタートしていない場合、列番号とCOLUMNの差を考慮しなければならず、例えばCスタートの場合はCOLUMN-2とする。	5
3	2	5
4	3	3
5
6
7
8
9
10

＄で範囲を指定する意味

上記の場合において＄とせずに、マウスドラッグで拡張した場合、C2とD2での範囲の部分は以下のようになるため、結果として、＃N/Aとなる
$A$2:$B$4としておけば、D2でも範囲が変わらず変わらず$A$2:$B$4となるため、2のあるA3行の2列目であるB3の5が表示される

	A	B	C	D	E
1
2	1	4	A2:B4	B2:C4
3	2	5
4	3	3

VLOOKUPで別シートから取得するには

別シート指定は「シート名!セル名」とすればよい。例えば上記A2:B4の内容を「データシート」という名称で別シートに保存しているとすると以下のようにして呼び出しを行う

	A	B	C	D	E
3				2 =VLOOKUP(E1, データシート!$A$2:$B$4, COLUMN(A2), FALSE) ”データシート”と””で囲む必要はない	2
4
5
6
7

VLOOKUPで複数結果に対応する

複数の結果に対応するには、COUNTIFで算出した何個目かの数値と検索値を結合した文字列を新たな検索値として設定します。新たな検索値を用意することで重複しないデータとして扱います。
COUNTIFを用いて、A列に該当するB列の文字が何番目かを表示し、Bと結合します。B4で2個目の1が表示されるためA4に21(2と1を結合)が表示されています。A1に=COUNTIF($B$1:B1,B1)&B1とし、A6までマウスで拡張します。A4は=COUNTIF($B$1:B4,B4)&B4となり、B4までB4が２こあるため、A4に21が表示されるわけです。
その後、VLOOKUPを使用します。以下は2個目の3、つまりFを表記させる方法です。
D2の部分を変更すれば、1個目、2個目、3個目と複数のセル表示することもできる。

	A	B	C	D	E
1	11	1	A	3	←探す数(B列)
2	12	2	B	2	←何個目？
3	13	3	C	F =VLOOKUP(D2&$D$1,$A$1:$C$6,3,FALSE))
4	21	1	D
5	14	4	E
6	23	3	F
7			A4は=COUNTIF($B$1:B4,B4)&B4とし、B1からB4までの1の個数をカウント(2)し、その数と結合し、”21″としています。
8
9
10
11

1を探す場合

1個目	=VLOOKUP(1&$D$1,$A$1:$C$6,3,FALSE))→A
2個目	=VLOOKUP(2&$D$1,$A$1:$C$6,3,FALSE))→D
3個目	=VLOOKUP(3&$D$1,$A$1:$C$6,3,FALSE))→#N/A

VLOOKUPに関する参考解説

Excelの関数を検索する

分類	関数	説明
日付	DATE	年月日から日付を求める
日付	DATEDIF	開始日から終了日までの期間を求める
日付	DAY	日付から日を抽出する
日付	EDATE	指定月後の月日を求める
日付	EOMONTH	指定月後の月末を求める
日付	EOMONTH+1	指定月後の月初めを求める
日付	HOUR	時刻から時間を取り出す
日付	MINUTE	時刻から分を取り出す
日付	MONTH	日付から付きを抽出する
日付	NETWORKDAYS	営業日数を求める(土日休日型)
日付	NETWORKDAYS.INTL	営業日数を求める(定休日と休業日指定型)
日付	NOW	今日の日付と時刻を求める
日付	SECOND	時刻から秒を取り出す
日付	TIME	時間を指定して時刻データを作成する
日付	TODAY	今日の日付を求める
日付	WEEKNUM	1月1日から何週目かを求める
日付	WEEKDAY	年月日から曜日番号を求める
日付	WORKDAY	営業日について指定した日数後の年月日を求める
日付	WORKDAY.INTL	営業日について指定した日数後の年月日を求める(定休日と休業日指定型)
日付	YEAR	日付から年を抽出する
文字操作	&	文字列を結合する
文字操作	ASC	全角文字を半角文字に置き換える
文字操作	CHAR/CLEAN	改行を削除する
文字操作	CONCAT	文字を範囲指定で結合する
文字操作	CONCATENATE	文字列を結合する
文字操作	COLUMN	列番号を取得する
文字操作	DELTA	数値が等しいかを判定する
文字操作	EXACT	文字列が等しいかを判定する
文字操作	FIND(B)	セル内の指定した文字の位置を求める
文字操作	ISODD	指定した数値が奇数か偶数かを判定する
文字操作	JIS	半角文字を全角文字に置き換える
文字操作	LEFT(B)	指定した文字数分の文字を先頭から取り出す
文字操作	LEN(B)	文字数をカウントする
文字操作	LOWER	文字列を小文字にする
文字操作	MID(B)	指定した文字以降の文字を取り出す
文字操作	NUMBERSTRING	漢数字に変換する
文字操作	PHONETIC	フリガナを取り出す
文字操作	PROPER	文字列の先頭を大文字にする
文字操作	REPLACE(B)	指定した位置にある文字を置き換える
文字操作	REPT	指定回数分の文字列を表示
文字操作	RIGHT(B)	指定した文字数分の文字を末尾から取り出す
文字操作	ROW	行番号を取得する
文字操作	SUBSTITUTE	指定した文字列を置き換える
文字操作	SEARCH(B)	セル内の指定した文字の位置を求める
文字操作	TEXT	日付などの数値(シリアル値)を文字形式に変換、抽出する
文字操作	TEXTJOIN	文字列を結合する
文字操作	TRIM	余分な空白を削除する
文字操作	UPPER	文字列を大文字にする
文字操作	VALUE	文字列を数値(シリアル値など)に変換する
論理	論理式	TRUEとFALSE
論理	AND	すべての条件を満たす論理式
論理	IF	論理式の結果によって表示を切り替える
論理	IFS	複数の論理式の結果によって表示を切り替える
論理	OR	いずれかの条件を満たす論理式
論理	SWITCH	論理式の結果によって表示を切り替える
数学	AGGREGATE	指定した19の集計方法で集計値を求める
数学	CEILING	基準値の単位で切り上げる
数学	FLOOR	基準値の単位で切り下げる
数学	INT	小数点以下を切り捨てる
数学	ISEVEN	偶数柿数かを判定する
数学	MOD	割り算の余りを求める
数学	MROUND	基準値の単位で四捨五入する
数学	POWER	べき乗を求める
数学	PRODUCT	掛け算を求める
数学	QUOTIENT	割り算の商を求める
数学	RAND	乱数を発生させる
数学	ROUND	指定した桁で四捨五入する
数学	ROUNDDOWN	指定した桁で切り捨てる
数学	ROUNDUP	指定した桁で切り上げる
数学	SUBTOTAL	指定した11の集計方法で集計値を求める
数学	SUM	数値の合計を求める
数学	SUMIF	特定の条件を満たすセルの合計値を求める
数学	SUMIFS	複数の条件を満たすセルの合計値を求める
数学	SUMPRODUCT	条件付きセルの乗算数の合計を求める
数学	SUMPRODUCT	セルの乗算数の合計を求める
数学	TRUNC	指定した桁数を切り捨てる
数学	TRUNC	小数点以下を切り捨てる
検索	CHOOSE	インデックス数値に対応する値を表示する
検索	FILTER	条件に一致する行のデータを求める
検索	INDEX	範囲内の指定したデータを取り出す
検索	INDEX	指定した行位置に対応する列データを取り出す
検索	INDEX	指定した列位置に対応する行データを取り出す
検索	LOOKUP	検索値に一致するデータを検索する
検索	MATCH	値に一致する行位置を求める
検索	MATCH	値に一致する列位置を求める
検索	UNIQUE	重複するデータをまとめる
検索	VLOOKUP	検索値に一致するデータを検索する
検索	VLOOKUP	検索値に対応するデータ(範囲型)を求める
統計	AVERAGE	平均値を求める
統計	AVERAGEA	平均値を求める
統計	AVERAGEIF	条件に合う数値の平均値を求める
統計	AVERAGEIFS	複数の条件に合う数値の平均値を求める
統計	COUNT	指定したデータの個数を求める
統計	COUNTA	指定したデータの個数を求める
統計	COUNTBLANK	空白セルの個数を求める
統計	COUNTIF	条件に合うデータ数を求める
統計	COUNTIFS	複数の条件に合うデータ数を求める
統計	FREQUENCY	データの度数を求める
統計	LARGE	大きいほうからの順位の数値を求める
統計	MAX	データの最大値を求める
統計	MAXA	データの最大値を求める
統計	MAXIFS	複数条件を満たす最大値を求める
統計	MEDIAN	中央値を求める
統計	MIN	データの最大値を求める
統計	MINA	データの最大値を求める
統計	MINIFS	複数条件を満たす最小値を求める
統計	MODE.MULT	データの最頻値を求める(こちらを推奨)
統計	MODE.SNGL	データの最頻値を求める(MODE.MULT推奨)
統計	PERCENTILE.EXC	百分位数(パーセント)を求める
統計	PERCENTILE.INC	百分位数(パーセント)を求める
統計	PERCENTRANK.EXC	データの位置を百分率で求める
統計	PERCENTRANK.INC	データの位置を百分率で求める
統計	QUARTILE.EXC	四分位数を求める
統計	QUARTILE.INC	四分位数を求める
統計	RANK.EQ	データの順位を求める
統計	SMALL	小さいほうからの順位の数値を求める
統計	STDEV.P	標準偏差(母集団型)を求める
統計	STDEV.S	標準偏差(標本型)を求める
統計	STDEVA	標準偏差(標本型で論理等含む)を求める
統計	STDEVPA	標準偏差(母集団型で論理等含む)を求める
統計	VAR.P	データの分散(標本分散)を求める
統計	VAR.S	データの分散(不偏分散)を求める
統計	VARA	データの分散(不偏分散)を求める
統計	VARPA	データの分散(標本分散)を求める