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

以下の表で説明します。

D2の検索値で、A列を検索し、一致した行のB列を取り出します。
この場合は、VLOOKUPを使用する事が通常です。
VLOOKUPが使用出来ないような場合に、他の関数を使って同様の機能を実現する必要が出てきます。
その時の基本として、VLOOKUPを他の関数で実現する、さまざまな方法を紹介します。
以下の順に説明します。
基本のVLOOKUP関数
INDEXとMATCHの組み合わせ
OFFSETとMATCHの組み合わせ

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

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

上記の数式は、
検索値"B02"（D2の値）で、範囲（A2～B7）の左端の列（A列）を検索し、
一致した行の、2列目のデータを取得しています。
図解すると、

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

=INDEX($A$2:$B$7,MATCH($D$2,$A$2:$A$7,0),2)
MATCH関数は、
指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。

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

上記の数式は、
検索値"B02"（D2の値）で、
範囲（A2～A7）を検査し、
一致した行の、A2からの位置（A2が1、A3が2）である4が返されます。
図解むすると、

"B02"（D2）で、A列を探し、一致した5行目（A5）の、A2からの位置である4を返します。
この4を使用して、以下のINDEX関数で値を取得します。
INDEX関数は、
行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。

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

上記の数式は、
範囲（A2～A7）の、4（MATCHの結果）番目の行、2番目の列、セルB5の値が取得されます。
図解すると、


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

=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を他の関数でやる方法 」をご覧下さい。

MATCH関数
INDEX関数
OFFSET関数
この３つの関数が自在に使いこなせれば、作成するエクセルの幅がぐっと広がります。
特に、OFFSET関数とCOUNTA関数を組み合わせ、検査するセル範囲を可変にすることで、
さらに次の次元のエクセルへと進む事が出来ます。
OFFSET関数とCOUNTA関数の組み合わせは、以下を参照してください。
セルの参照範囲を可変にする（OFFSET、COUNTA、MATCH）

同じテーマの記事
新着記事
アクセスランク