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

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

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


エクセルの関数の中で頻繁に使われるVLOOKUP、非常に便利なのですが、
キー列(検索値を探す列)より左側にある列を取得できません、
この仕様は時に不便で、左側の列を取得したい場合には他の関数を使って実現します。



まずは、VLOOKUPの説明から

VLOOKUP関数

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

書式
 =VLOOKUP(検索値,範囲,列番号,検索方法)

検索値
表または範囲の左端の列で検索する値を指定します。
※ワイルドカードが使用できますが、ほぼ使う事はありません。

範囲
データを含むセル範囲です。

列番号
目的のデータが入力されている列を、範囲内の左端から数えた列数で指定します。

検索方法
TRUE(1)を指定すると、 検索値と完全に一致する値、またはその近似値(検索値未満の最大値)が返されます。
※範囲の左端の列にある値を昇順に並べ替えておく必要があります。
FALSE(0)を指定すると 検索値と完全に一致する値だけが検索されます。
完全に一致する値が範囲の左端の列に複数ある場合は、最初に見つかった値が使用されます。
完全に一致する値が見つからない場合は、エラー値 #N/A が返されます。
※TRUE(1)の使い方は少し難しいです、ほとんどの場合、FALSE(0)で良いはずです。  


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


Excelサンプル

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

そこで使用する関数が、
MATCH
INDEX
OFFSET

になります。


MATCH関数

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

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

検査値
値 (数値、文字列、または論理値)、またはこれらの値に対するセル参照を指定できます。

検査範囲
検索するセルの範囲を指定します。 縦方向・横方向のどちらでも指定可能ですが、1列または1行のみになります。

照合の型
1
検査値以下の最大の値が検索されます。
このとき検査範囲のデータは、昇順に並べ替えておく必要があります。

0
検査値に一致する値のみが検索の対象となります。
このとき検査範囲を並べ替えておく必要はありません。
完全に一致する値が見つからない場合は、エラー値 #N/A が返されます。
※0の場合のみ、ワイルドカードが使用できます。

-1

検査値以上の最小の値が検索されます。
このとき検査範囲のデータは、降順に並べ替えておく必要があります。


INDEX関数

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

書式
INDEX(範囲, 行番号[, 列番号])

範囲 セル範囲を指定します。

セル範囲が 1 行または 1 列である場合、行番号または列番号はそれぞれ省略することができます。
範囲が 1 列のみである場合は、INDEX(範囲,行番号) 範囲が 1 行のみである場合は、INDEX(範囲,列番号)

行番号
範囲の中にあり、セル参照を返すセルの行位置を数値で指定します。

列番号
範囲の中にあり、セル参照を返すセルの列位置を数値で指定します。


OFFSET関数

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

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

基準
基準となるセル範囲の参照を指定します。

行数
基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。
行数に正の数を指定すると下方向へシフトし、負の数を指定すると上方向へシフトします。

列数
基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。
列数に正の数を指定すると右方向へシフトし、負の数を指定すると左方向へシフトします。

高さ
オフセット参照の行数を指定します。高さは正の数である必要があります。


オフセット参照の列数を指定します。幅は正の数である必要があります。


 これで必要な関数が出そろいました。
これらの関数を組み合わせることで、
VLOOKUPではできない、左側の列を取得できます。



MATCH関数とINDEX関数を使う

Excelサンプル


Excelサンプル

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


MATCH関数とOFFSET関数を使う

Excelサンプル

Excelサンプル

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


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


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

徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
検索されるキーワードで最も多いのがMATCH関数セルの範囲内で指定された項目を検索しその項目の相対的な位置を返します。セル範囲は縦方向・横方向のどちらでも指定可能です。書式MATCH(検査値,検査範囲,照合の型)検査値値(数値文字列または論理値)またはこれらの値に対するセル参照を指定できます。




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

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

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

Withステートメントの実行速度と注意点|VBA技術解説(6月6日)
VBA+SeleniumBasicで検索順位チェッカー(改)|VBA技術解説(6月2日)
マクロでShift_JIS文字コードか判定する|VBA技術解説(6月1日)
Shift_JISのテキストファイルをUTF-8に一括変換|VBAサンプル集(5月31日)
「VBAによる解析シリーズその2 カッコ」をやってみた|エクセル(5月21日)
VBA+SeleniumBasicで検索順位チェッカー作成|VBA技術解説(5月18日)
テーブル操作のVBAコード(ListObject)|VBA入門(5月12日)
テーブル操作の概要(ListObject)|VBA入門(5月12日)
VBAのスクレイピングを簡単楽にしてくれるSelenium|VBA技術解説(5月6日)
Excelワークシート関数一覧(2010以降)|VBAリファレンス(4月22日)

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

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



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

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


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






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

    本文下部へ