エクセル関数超技
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)

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

VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)


エクセルの数ある関数の中でも頻繁に使われるVLOOKUP関数は非常に便利な関数ですが、
キー列(検索値を探す列)より左側にある列を取得できません。


これは仕様で仕方ないのですが時に不便な場合があります。
キー列より左側の列を取得したい場合には、VLOOKUP関数ではなく他の関数を使って実現します。
まずはVLOOKUP関数を確認してから、別の方法について解説します。

VLOOKUP関数

検索値で、セル範囲の最初の列を検索し、その範囲の同じ行にある任意のセルから値を返します。
Vは縦方向(vertical)の意味です、つまり縦方向の表に対して使用します。

=VLOOKUP(検索値,範囲,列番号,検索方法)
検索値
表または範囲の左端の列で検索する値を指定します。
※ワイルドカードが使用できますが、使う事は少ないと思います。
範囲
データを含むセル範囲です。
列番号
目的のデータが入力されている列を、範囲内の左端から数えた列数で指定します。
検索方法
TRUE(1)を指定すると、 検索値と完全に一致する値、またはその近似値(検索値未満の最大値)が返されます。
※範囲の左端の列にある値を昇順に並べ替えておく必要があります。
FALSE(0)を指定すると 検索値と完全に一致する値だけが検索されます。
完全に一致する値が範囲の左端の列に複数ある場合は、最初に見つかった値が使用されます。
完全に一致する値が見つからない場合は、エラー値 #N/A が返されます。
※TRUE(1)の使い方は少し難しいです、ほとんどの場合、FALSE(0)で良いはずです。
VLOOKUP関数の詳細については以下を参照してください。
VLOOKUP関数
文字列中の指定された文字数の文字を別の文字に置き換えます。書式 REPLACE(文字列,開始位置,文字数,置換文字列) 文字列 置き換えを行う文字列を指定します。開始位置 置換文字列と置き換える先頭文字の位置(文字番号)を数値で指定します。

キー列より左側の列を取得したい

では、本題のキーより左の列を取得したい場合です。
以下の表で説明します。

Excelサンプル

この表を元に、説明していきます。
この表の場合、キーとなる列Cより左側(A列B列)のデータを取得することになり、

VLOOKUPでは取得できないことになります。
そこで使用する関数が、
MATCH
INDEX
OFFSET

になります。
それぞれの関数を簡単に確認してから、
これらの関数を組み合わせて、キー列より左側の列を取得する方法を解説します。

MATCH関数

セルの範囲内で指定された項目を検索し、その項目の相対的な位置を返します。
セル範囲は、縦方向・横方向のどちらでも指定可能です。

MATCH(検査値, 検査範囲,照合の型)
検査値
値 (数値、文字列、または論理値)、またはこれらの値に対するセル参照を指定できます。
検査範囲
検索するセルの範囲を指定します。 縦方向・横方向のどちらでも指定可能ですが、1列または1行のみになります。
照合の型
1 - 以下
検査値以下の最大の値が検索されます。
このとき検査範囲のデータは、昇順に並べ替えておく必要があります。
0 - 完全一致
検査値に一致する値のみが検索の対象となります。
このとき検査範囲を並べ替えておく必要はありません。
完全に一致する値が見つからない場合は、エラー値 #N/A が返されます。
※0の場合のみ、ワイルドカードが使用できます。
-1 - 以上
検査値以上の最小の値が検索されます。
このとき検査範囲のデータは、降順に並べ替えておく必要があります。

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

INDEX関数

セル範囲から、指定された行と列が交差する位置にあるセルの参照を返します。

INDEX(範囲, 行番号[, 列番号])
範囲
セル範囲を指定します。
セル範囲が 1 行または 1 列である場合、行番号または列番号はそれぞれ省略することができます。
範囲が 1 列のみである場合は、INDEX(範囲,行番号) 範囲が 1 行のみである場合は、INDEX(範囲,列番号)
行番号
範囲の中にあり、セル参照を返すセルの行位置を数値で指定します。
列番号
範囲の中にあり、セル参照を返すセルの列位置を数値で指定します。

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

OFFSET関数

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

OFFSET(基準, 行数, 列数, 高さ, 幅)

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

OFFSET関数の詳細は、以下を参照してください。

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


 これで必要な関数が出そろいました。


これらの関数を組み合わせることで、
VLOOKUP関数ではできない、キー列より左側の列の値を取得できます。

MATCH関数とINDEX関数を使う

エクセル VLOOKUP 左を取得

E2セルの値でC;zを検索して、一致した行のA列またはB列を取得します。
MATCH関数とINDEX関数を組み合わせます。

エクセル VLOOKUP 左を取得

=INDEX($A:$B,MATCH($E$2,$C:$C,0),1)
=INDEX($A:$B,MATCH($E$2,$C:$C,0),2)

赤字の部分が、取得する列位置の指定になります。

MATCH関数でC列を検索して行位置を取得します。
その行位置を使って、INDEX関数でA列またはB列を取得しています。

MATCH関数とOFFSET関数を使う

エクセル VLOOKUP 左を取得

E2セルの値でC;zを検索して、一致した行のA列またはB列を取得します。
MATCH関数とOFFSET関数を組み合わせます。

エクセル VLOOKUP 左を取得

=OFFSET($A$1,MATCH($E$2,$C:$C,0)-1,0)
=OFFSET($A$1,MATCH($E$2,$C:$C,0)-1,1)

赤字の部分が、取得する列位置の指定になります。

MATCH関数でC列を検索して行位置を取得します。
その行位置を使って、OFFSET関数でA列またはB列を取得しています。

キー列より左側の列を取得のまとめ

INDEX関数とOFFSET関数については、どちらを使用しても大差はありません。
赤字の部分が、列位置の指定になりますが、
上記のような表の場合は、
列数の指定値を考えると、INDEX関数のほうがVLOOKUPからの書き直しとしては見やすいと思います。

出来上がりの関数については、あえて詳細な解説は省きます。
一つ一つの関数を読み解いて、ご自身で納得していくことで、さらに応用力が身につくはずです。

VLOOKUP関数、MATCH関数、INDEX関数、OFFSET関数、これらの詳しい解説については、
以下のページでわかりやすく解説しています。
徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
検索されるキーワードで最も多いのが、MATCH関数 INDEX関数セル範囲から、指定された行と列が交差する位置にあるセルの参照を返します。書式INDEX(範囲,行番号[,列番号])範囲セル範囲を指定します。セル範囲が1行または1列である場合、行番号または列番号はそれぞれ省略することができます。



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

選択行の色を変える(条件付き書式,Worksheet_SelectionChange)
他ブックを参照できる関数、他ブックを参照できない関数
時間計算で困ったときの確実な対処方法
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
数値範囲で表検索するVLOOKUP近似一致
エクセルの日付と時刻のまとめ


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

Byte配列と文字コード関数について|VBA技術解説(8月20日)
PowerQueryの強力な機能をVBAから利用する方法|VBA技術解説(8月4日)
練習問題31(セル結合を解除して値を埋める)|VBA練習問題(7月30日)
練習問題30(マトリックス→リスト形式)|VBA練習問題(7月25日)
Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説



  • >
  • >
  • >
  • VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)

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


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




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