OFFSET・INDEX・MATCHでVLOOKUPに出来ないマイナス方向にある列の値を取得しよう

Excelの関数みなさんつかってますか？

関数が使えるようになるとExcelでできる幅が飛躍的に広がるのでぜひ少しづつでもいいので覚えていきたいですね。

でも、たくさんありすぎるくらいあるのでどれから覚えていけば、、、

という方は、実際使う関数は限られてきますのでこちらの記事を参考にされるといいかもしれません。
http://tonari-it.com/excel-function/

さてそろそろ本題です、検索系の関数の中でまちがいなく使用頻度上位ランキングに食い込んでくる「VLOOKUP」

VLOOKUPは配列の左端列で特定の値を検索し、対応するセルの値を返してくれる関数です。

めちゃくちゃ便利で初心者から上級者までお世話になる関数ですが、弱点として検索範囲の左端の列から右方向にしか検索がかけられないという点があります。

今回は、そんなVLOOKUPの弱点であるマイナス方向への検索が可能な関数の組み合わせ技として「OFFSET・MATCH」と「INDEX・MATCH」をでの２通りの検索方法をご紹介いたします。

なお、基本であるVLOOKUPの説明に関してはこちらの記事をご覧ください
http://tonari-it.com/vlookup/

VLOOKUPでマイナス方向への検索はできない

マイナス方向とは簡単に言うと検索値より「左方向（上方向）」という意味です。

通常VLOOKUPは検索範囲の一番左の列から指定した列数目の行の値を返します。

そのためなので、その列より右側にある列の値しか取得することが出来ません。
列番号に「マイナスの値」をいれたらいどうでしょうか？

サンプルExcel→offset_index_sample

はい、エラー(#VALUE!)がでてしまいました。

あくまでVLOOKUPは検索範囲の一番左端の列からプラス方向へ指定した列数の値を返すことしかできないのです。

じゃぁ、検索列よりマイナス方向の値が欲しい場合はどうすればいいの！？

結果的にはVLOOKUPではできないので諦めます！

が、別の方法で検索は可能なのでここでは冒頭でご紹介した2種類の方法をご説明いたします。

OFFSET関数・INDEX関数・MATCH関数とは？

まず、検索方法に入る前にそれぞれの関数を簡単にご説明します。

既に知ってるよというかたはそのままスルーをしてください。

OFFSET関数

OFFSETは基準にて指定したセルより、指定された行数と列数だけシフトした位置にあるセルへの参照値を返す関数です。

サンプルExcel→9masu

３×３の表を用意しました。

セル「B2」を基準とし、セル「D4」の値をセル「F４」に表示させるには次のような数式を入力します。

これは、セル「B2」から2行・2列シフトした値を参照するという意味です。セル「F4」に「D4」が見事参照されました。

INDEX関数

INDEXは範囲内で指定した行と列が交差する位置の値を返す関数です。

範囲をセル「B2：D4」として、セル「D4」の値をセル「F4」に表示させるには次のような数式を入力します。

これは、指定した範囲の左上から数えて3行目の3列目の位置にあるセルの値を参照するという意味となり、セル「D4」がこちらも見事参照されました。

OFFSETが基準からシフトした位置を参照するのに対して、INDEXは指定した範囲内の位置を参照する関数となります。

MATCH関数

なんとなくVLOOKUPと項目が似ていますが、VLOOKUPが値を返すのに対してMATCHは検査値と一致するセルの相対的な位置を返します。

上図ではセル「G4」に以下の数式を入力します。

セル「F4」を検索値として、B2:B4の範囲の中で検索すると完全一致するのは上から3つ目ということで「３」が返されました。

最後の「０」は完全一致という意味となり、他にも検索値以下の最大・最小を求めることも可能です。

OFFSET・MATCHでの検索方法

OFFSETはご説明した通り、基準セルから指定した行数・列数シフトした位置のセルを参照します。

これに検索値と一致するセルの位置を数値で返すMATCHを組み合わせてマイナス方向にある列を検索してみましょう。

offset_index_sampleを用いて、県庁所在地を検索値として都道府県を表示させます。

セル「E7」へ都道府県名を表示させたいので次の数式を入力します。

セル「C2」を基準として、MATCHで返された数値だけ行をシフトし、列を－１つまり左方向へ１つ列をシフトした位置のセルを参照するという意味になり、結果としてセル「B４」の東京都が参照されます。

この時、注意したいのが基準に設定するセルです。

MATCHの結果に「０」はないので、基準とするセルをOFFSETの検索範囲内にしていまうとずれが生じてしまいますので、一段上の行を設定しましょう。

INDEX・MATCHでの検索方法

INDEXは範囲内で指定した行と列が交差する位置の値を参照します。

先ほどと同じ表を使用して、これにMATCHを組み合わせマイナス方向にある列を検索するにはセル「E7」へ次の数式を入力します。

セル「B3:B7」を範囲として、MATCHで返された数値の行目のセルを参照するという意味となり、範囲の２行目にあるセル「B4」の東京都を参照します。

また、INDEXは範囲が1列だけの場合は列番号省略することが出来るので、今回範囲がB列だけだったので行番号のみで参照することが出来ました。

さいごに

お気づきの方もいるかとおもいますが、VLOOKUPだけではなくHLOOKUPにも応用が今回の数式は可能なので、興味のある方は試してみてください。

関数の組み合わせはここだけに関わらず、Excelを使い込んでいくとよく使う手法になってきます。

その中でもOFFSETは単体としはほとんど使用しない関数ですが、今回のMATCHのような他の関数と組み合わせることで力を発揮できる特殊な関数ともいえます。

また、説明の中では触れませんでしたが幅・高さを指定して、範囲で結果を返すことができるというかこの使用方法の方が多いかと思いますので別の機会があればご案内したいと思います。