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

最終更新日：2019-06-16

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

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

MATCH関数

セルの範囲内で指定された項目を検索し、その項目の相対的な位置を返します。セル範囲は、縦方向・横方向のどちらでも指定可能です。書式 MATCH(検査値,検査範囲,照合の型) 検査値値(数値、文字列、または論理値)、またはこれらの値に対するセル参照を指定できます。

INDEX関数

セル範囲から、指定された行と列が交差する位置にあるセルの参照を返します。書式 INDEX(範囲,行番号[,列番号]) 範囲セル範囲を指定します。セル範囲が1行または1列である場合、行番号または列番号はそれぞれ省略することができます。

OFFSET関数

基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある指定の高さと幅のセル範囲の参照を返します。つまり基準セルから指定数だけ移動したセルを起点として指定の大きさのセル範囲を取得出来ます。書式 OFFSET(基準,行数,列数,[高さ],[幅]) 基準基準となるセル範囲の参照を指定します。

VLOOKUP関数

検索値で、セル範囲の最初の列を検索し、その範囲の同じ行にある任意のセルから値を返します。Vは縦方向(vertical)の意味です、つまり縦方向の表に対して使用します。書式 =VLOOKUP(検索値,範囲,列番号,検索方法) 検索値表または範囲の左端の列で検索する値を指定します。

この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列目のデータを取得しています。

つまり、

"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が返されます。

つまり、

"B02"（D2）で、A列を探し、一致した5行目（A5）の、A2からの位置である4を返します。

この4を使用して、以下のINDEX関数で値を取得します。

INDEX関数は、

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

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

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

行番号
配列の中にあり、値を返す行を数値で指定します。
行番号を省略した場合は、必ず列番号を指定する必要があります。

列番号
配列の中にあり、値を返す列を数値で指定します。
列番号を省略した場合は、必ず行番号を指定する必要があります。

上の数式は、

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

つまり、

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の値が取得されます。

つまり、

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

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

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

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

ただし、OFFSET関数は、単独で使う事はほとんど無いでしょう、

他の関数との組み合わせで力を発揮する関数です。

VLOOKUPと同様の事を他の関数で実現する方法としては、

上記以外では、配列を使用した方法もあります。

配列による方法は、「VLOOKUPを他の関数でやる方法 」をご覧下さい。

VLOOKUPは便利な関数ですが以下のような問題点がある場合はこれを使うことができません問題点１．検索列より左側の列を取り出す場合２．検索値が255文字を超える時そこで他の関数でこれを実現する方法を検討します。方法１ =INDEX(範囲,MATCH(検索値,検索範囲,0),列番号) 方法２ {=IF(MIN(IF(検索範囲=検索値,

MATCH関数
INDEX関数
OFFSET関数

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

特に、OFFSET関数とCOUNTA関数を組み合わせ、検査するセル範囲を可変にすることで、

さらに次の次元のエクセルへと進む事が出来ます。

OFFSET関数とCOUNTA関数の組み合わせは、

セルの参照範囲を可変にする（OFFSET、COUNTA、MATCH）

数式の参照範囲を可変対応させます、つまり、入力データにより自動で拡張・縮小されるように関数を設定します。エクセルの表には、集計等の計算式を入れますが、その集計範囲は固定になっているため、データの追加時には、計算範囲外になってしまったりするので注意が必要になります。