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

Excel関数の解説、関数サンプルと高等テクニック
公開日:2013年5月以前 最終更新日: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関数の問題点 ・解決方法 ・解決方法の具体例 ・解決方法ごとの長所短所
誕生日一覧から、指定誕生月の人を全員取り出す
配列数式を使って、誕生日が指定月の該当者全員を取り出します。各種条件でデータを絞る場合は、オートフィルタが一般的に使用されますが、日付の中の、月だけの指定になると、お手上げです。(出来ない事はありませんが、かなり面倒です。
徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)
・説明で使用するエクセル表 ・OFFSET関数 ・MATCH関数 ・COUNTA関数 ・セルの参照範囲を可変にする数式の解説 ・全合計(E3)の数式 ・開始日(E4)と終了日(E5)に名前定義とリストを設定 ・期間合計(E5)の数式 ・短く易しい数式で
グラフのデータ範囲を可変にする
データの範囲に合わせて、自動的にグラフのデータ範囲が変更されるようにします。グラフのデータ個数が増えるたびに、「データの選択」(2003は元データ)を変更するのは、いかにも面倒です。できれば、マクロでやりたいところですが、マクロはちょっという人用に解説します。
セルの個数を数える関数
セルの個数を数える関数を解説します COUNT関数 COUNTA関数 COUNTBLANK関数 COUNTIF関数 になります。以下の表で説明します。COUNT関数 これは、数値のみカウントします。セルの書式や、先頭に「'」等があっても数値ならカウントされます。
【奥義】大量データでの高速VLOOKUP
・高速VLOOKUPに使用するサンプルデータ ・高速VLOOKUPの数式 ・高速VLOOKUPの数式解説 ・高速VLOOKUPの補足
数値を時刻に変換
・数値を時刻に変換:方法1 ・数値を時刻に変換:方法2 ・数値を時刻に変換:方法3 ・数値を時刻に変換の注意点
関数のネスト方法
関数のネスト(入れ子)をする場合の、考え方と作成方法です。IF関数のネスト作成方法 A列の数値によって、B列に"A"から"E"の評価を入れます。・80以上はA ・60以上はB ・40以上はC ・20以上はD ・20未満はE この場合は、まず、=IF(A1>=80,
ワイルドカードが使える関数
・ワイルドカードについて ・データベース関数を除くExcel2003までの関数 ・データベース関数を除くExcel2007以降 ・2020年3月現在のOffice365 ・ワイルドカードの使用例


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

テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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