エクセル関数応用
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関数の書式 REPLACE(文字列,開始位置,文字数,置換文字列) 文字列 置き換えを行う文字列を指定します。開始位置 置換文字列と置き換える先頭文字の位置(文字番号)を数値で指定します。

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

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

Excelサンプル

この表を元に、説明していきます。
この表の場合、キーとなる列Cより左側(A列B列)のデータを取得することになり、
VLOOKUPでは取得できないことになります。
そこで使用する関数が、
MATCH
INDEX
OFFSET

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

MATCH関数

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

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

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

INDEX関数

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

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

INDEX関数ま詳細については、以下を参照してください。
INDEX関数
・INDEX関数の書式 ・INDEX関数の使用例 ・INDEX関数の応用例

OFFSET関数

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

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

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

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

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)
・解説に使うシート ・VLOOKUP関数 ・INDEX関数とMATCH関数の組み合わせ ・OFFSET関数とMATCH関数の組み合わせ ・VLOOKUPを他の関数でやる方法 ・徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)の最後に



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

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


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

抜けている数値を探せ|エクセル雑感(2022-07-01)
.Net FrameworkのSystem.Collectionsを利用|VBA技術解説(2022-06-29)
迷路ネコが影分身の術を体得したら…|エクセル雑感(2022-06-27)
迷路にネコが挑戦したら、どうなるかな…|エクセル雑感(2022-06-26)
サロゲートペアに対応した自作関数(Len,Left,Mid,Right)|エクセル雑感(2022-06-24)
「マクロの登録」で登録できないプロシージャーは?|エクセル雑感(2022-06-23)
オブジェクトのByRef、ByVal、Variant|エクセル雑感(2022-06-22)
コメントから特定形式の年月を取り出す|エクセル雑感(2022-06-19)
4,9を使わない連番作成|エクセル雑感(2022-06-17)
連番を折り返して出力|エクセル雑感(2022-06-16)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.マクロって何?VBAって何?|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




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


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



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