エクセル関数応用
徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)

Excel関数の解説、関数サンプルと高等テクニック
最終更新日:2021-03-08

徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)


検索されるキーワードで最も多いのが、


MATCH関数
・MATCH関数の書式 ・ワイルドカード ・MATCH関数の使用例:完全一致
INDEX関数
・INDEX関数の書式 ・INDEX関数の使用例 ・INDEX関数の応用例
OFFSET関数
・OFFSET関数の書式 ・OFFSET関数の解説 ・OFFSET関数の使用例 ・OFFSET関数の応用例
VLOOKUP関数
・VLOOKUP関数の書式 ・VLOOKUP関数:近似一致(検索方法=TRUE)の使用例 ・VLOOKUP関数:完全一致(検索方法=FALSE)の使用例

この4つの関数です
そこで、これらの関数を徹底解説します。


解説に使うシート

以下の表で説明します。

Excel VBA 解説

D2の検索値で、A列を検索し、一致した行のB列を取り出します。

この場合は、VLOOKUPを使用する事が通常です。
VLOOKUPが使用出来ないような場合に、他の関数を使って同様の機能を実現する必要が出てきます。
その時の基本として、VLOOKUPを他の関数で実現する、さまざまな方法を紹介します。

以下の順に説明します。

基本のVLOOKUP関数
INDEXとMATCHの組み合わせ
OFFSETとMATCHの組み合わせ


VLOOKUP関数

=VLOOKUP($D$2,$A$2:$B$7,2,FALSE)

VLOOKUP関数は、
指定された範囲の 1 列目で特定の値を検索し、その範囲内の別の列の同じ行にある値を返します。

VLOOKUP(検索値,範囲,列番号,検索の型)
検索値
範囲の左端の列で検索する値を指定します。
検索値には、値またはセル参照を指定します。
範囲
セル範囲を指定します。
範囲の左端の列の値が、検索値で検索される値です。
列番号
範囲内で目的のデータが入力されている列を、左端からの列数で指定します。
検索の型
TRUEまたは省略すると、検索値未満の最大値が使用されます。
昇順に並べ替えておく必要があります。
FALSEを指定すると、検索値と完全に一致する値だけが検索されます。
検索値と一致する値が見つからない場合は、#N/Aエラー値が返されます。

上記の数式は、

検索値"B02"(D2の値)で、範囲(A2~B7)の左端の列(A列)を検索し、
一致した行の、2列目のデータを取得しています。

図解すると、

Excel VBA 解説

"B02"(D2)で、A列を探し、一致した5行目(A5)の、2列目(B5)を取得しています。


INDEX関数とMATCH関数の組み合わせ

=INDEX($A$2:$B$7,MATCH($D$2,$A$2:$A$7,0),2)

MATCH関数は、
指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。

MATCH(検査値,検査範囲,照合の型)

検査値
表の中で必要な項目を検索するために使用する値を指定します。
検査範囲
検査する隣接したセル範囲を指定します。
検索の型
-1、0、1 の数値のいずれかを指定します。
1を指定すると、検査値以下の最大の値が検索されます。
0を指定すると、検査値に一致する値のみが検索の対象となります。
-1を指定すると、検査値以上の最小の値が検索されます。
省略すると1を指定したものと見なされます。

上記の数式は、

検索値"B02"(D2の値)で、
範囲(A2~A7)を検査し、
一致した行の、A2からの位置(A2が1、A3が2)である4が返されます。

図解むすると、

Excel VBA 解説

"B02"(D2)で、A列を探し、一致した5行目(A5)の、A2からの位置である4を返します。
この4を使用して、以下のINDEX関数で値を取得します。

INDEX関数は、
行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。

INDEX(配列,行番号,列番号)

配列
セル範囲または配列定数を指定します。
配列が1行または1列のみの場合、それぞれ行番号または列番号を省略することができます。
行番号
配列の中にあり、値を返す行を数値で指定します。
行番号を省略した場合は、必ず列番号を指定する必要があります。
列番号
配列の中にあり、値を返す列を数値で指定します。
列番号を省略した場合は、必ず行番号を指定する必要があります。

上記の数式は、

範囲(A2~A7)の、4(MATCHの結果)番目の行、2番目の列、セルB5の値が取得されます。

図解すると、

Excel VBA 解説

A2~A7の範囲で、上から4番目、左から2番目のセル(B5)を取得します。



OFFSET関数とMATCH関数の組み合わせ

=OFFSET($A$2,MATCH($D$2,$A$2:$A$7,0)-1,1,1,1)

MATCH関数は、INDEXとMATCHの組み合わせと同じです。

OFFSET関数は、

基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照 (オフセット参照) を返します。

OFFSET(基準,行数,列数,高さ,幅)
基準
基準となるセル範囲の参照を指定します。
行数
基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。
列数
基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。
高さ
オフセット参照の行数を指定します。高さは正の数である必要があります。
オフセット参照の列数を指定します。幅は正の数である必要があります。

上記の数式は、

基準(A2)から、3(MATCHの結果4-1)下方向へシフトし、1右にシフトした、B5の値が取得されます。

図解すると、

Excel VBA 解説

A2から、下に3、右に1ずれた、B5を取得します。

ここでは、高さ1、幅1を指定していますので、1つのセルが取得されています。

OFFSETの使い方としては特殊な使い方になっています。

通常は、高さ、幅を指定して、セル範囲を返す関数として使用します。

ただし、OFFSET関数は、単独で使う事はほとんど無いでしょう、
他の関数との組み合わせで力を発揮する関数です。


VLOOKUPを他の関数でやる方法

VLOOKUPと同様の事を他の関数で実現する方法としては、
上記以外では、配列を使用した方法もあります。

配列による方法は、「VLOOKUPを他の関数でやる方法 」をご覧下さい。
・VLOOKUP関数の問題点 ・解決方法 ・解決方法の具体例 ・解決方法ごとの長所短所


徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)の最後に

MATCH関数
INDEX関数
OFFSET関数


この3つの関数が自在に使いこなせれば、作成するエクセルの幅がぐっと広がります。

特に、OFFSET関数とCOUNTA関数を組み合わせ、検査するセル範囲を可変にすることで、
さらに次の次元のエクセルへと進む事が出来ます。

OFFSET関数とCOUNTA関数の組み合わせは、以下を参照してください。
セルの参照範囲を可変にする(OFFSET、COUNTA、MATCH
・説明で使用するエクセル表 ・OFFSET関数 ・MATCH関数 ・COUNTA関数 ・セルの参照範囲を可変にする数式の解説 ・全合計(E3)の数式 ・開始日(E4)と終了日(E5)に名前定義とリストを設定 ・期間合計(E5)の数式 ・短く易しい数式で




同じテーマ「エクセル関数応用」の記事

関数で他シートへ並べ替える方法(サンプル:ABC分析)
VLOOKUPを他の関数でやる方法
誕生日一覧から、指定誕生月の人を全員取り出す
徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)
グラフのデータ範囲を可変にする
セルの個数を数える関数
【奥義】大量データでの高速VLOOKUP
数値を時刻に変換
関数のネスト方法
ワイルドカードが使える関数


新着記事NEW ・・・新着記事一覧を見る

新旧マスタの差異比較|Power Query(M言語)入門(2023-02-28)
有効な最新単価の取得|Power Query(M言語)入門(2023-02-26)
有効な最新単価の取得|Power Query(M言語)入門(2023-02-21)
グルーブ内の最小・最大|Power Query(M言語)入門(2023-02-17)
2つのテーブルのマージ|Power Query(M言語)入門(2023-02-15)
「売上」が数値の行のみ取り込む|Power Query(M言語)入門(2023-02-13)
A列のヘッダー名を変更する|Power Query(M言語)入門(2023-02-11)
CSVのA列が日付の行だけを取り込む|Power Query(M言語)入門(2023-02-10)
列数不定のCSVの取り込み|Power Query(M言語)入門(2023-02-09)
別ブックの最終シートの取り込み|Power Query(M言語)入門(2023-02-08)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.ひらがな⇔カタカナの変換|エクセル基本操作
6.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.Excelショートカットキー一覧|Excelリファレンス
9.並べ替え(Sort)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ