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

最終更新日：2014-10-22

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

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

MATCH関数
INDEX関数

セル範囲から、指定された行と列が交差する位置にあるセルの参照を返します。書式INDEX(範囲,行番号[,列番号]) 範囲セル範囲を指定します。Excelの初心者向け入門解説

OFFSET関数

この３つの関数です

そこで、VLOOKUP関数も含めて、これらの関数を徹底解説します。

まず、以下の表で説明します。

Excel解説

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

MATCH関数
INDEX関数
OFFSET関数

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

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

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

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

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

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

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

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

1.ひらがな⇔カタカナの変換｜エクセル基本操作
2.最終行の取得（End,Rows.Count）｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
5.RangeとCellsの使い方｜ExcelマクロVBA入門
6.変数とデータ型（Dim）｜ExcelマクロVBA入門
7.マクロって何？VBAって何？｜ExcelマクロVBA入門
8.繰り返し処理（For Next)｜ExcelマクロVBA入門
9.とにかく書いて見よう（Sub,End Sub）｜VBA入門
10.セルに文字を入れるとは（Range,Value）｜VBA入門

トップ >

エクセル全般 >

エクセル関数超技 >

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

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

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

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