OFFSET関数とMATCH関数の組合せで、VLOOKUP関数ではできないマイナス方向の値を取得する

普段エクセルで仕事をしていると、VLOOKUP関数を使用する機会が多々あると思います。しかし、VLOOKUP関数には検索値よりマイナス方向の列にある値を取得することができないという弱点があります。

 上図では、「商品コード」を検索値として、右表から、「2018年度売上」の値を取得するために、VLOOKUP関数を使用しています。しかし、右表の「商品名」は検索値(商品コード)の左にあるためにVLOOKUP関数では値を取得することができません。

ちなみに、VLOOKUP関数の列番号を"-1"にすればできるのではないか？と誰もが一度は考えると思いますが、あえなくエラー(#VALUE!)になります。(笑)

 そこで今日は、検索値よりも左側の列にある値を取得するための関数を紹介していきます。

 OFFSET関数・MATCH関数の組合せ

 VLOOKUP関数の弱点を補完してくれるのが、OFFSET関数とMATCH関数の組み合わせになります。手っ取り早く結論から説明すると、下のような数式になります。

=OFFSET(基準,MATCH(検索値,検索範囲,[照合の型]),列数)

上の書式は、OFFSET関数にMATCH関数をネストしているのですが、これだけでは何のことかさっぱりだと思うので、また先ほどの図を見ていきます。

上表では、セルB4に

=OFFSET($N$4,MATCH(C4,$N$4:$N$14,0)-1,-1)

という式が入力されており、これを１４行目までコピーしています。

何をしているかというと、OFFSET関数の行数を入力するべきところにMATCH関数をネストすることで、右表の商品コードを基準に、その１つ左の値を左表のB列に返しています。

 検索値より2列左の列の値を取得したければ、OFFSET関数の列数の"-1"の部分が"-2"になります。(OFFSET関数の行数を"-1"している理由は後述します。)

 ちなみに、OFFSET関数の列数の"-1"の部分を"+1"にすると、VLOOKUP関数とまったく同じ結果が得られます。そのため、OFFSET関数とMATCH関数の組み合わせはいろいろな場面で活躍し、VLOOKUP関数よりも圧倒的に汎用性が高いのですが、職場でも使える人をほとんど見かけません。逆の見方をすると、マスターすればエクセル上級者の仲間入りができるということです。

 では、さらに理解を深めるためにOFFSET関数・MATCH関数をそれぞれ説明していきます。

 OFFSET関数

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

解説：セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル(セル範囲)の参照を返します。

言葉にすると非常にわかりにくいので、下図を参考にしてください。

これだけ見ると、OFFSET関数っていったい何に使うんだ？と疑問が浮かぶと思いますが、その直感は正しく、OFFSET関数が単独で力を発揮することは実務でまずお目にかかりません。この関数は他の関数とネストさせることで活躍してくれます。

では次にMATCH関数についてみていきます。

 MATCH関数

=MATCH(検査値, 検査範囲, [照合の型])

解説：セル範囲で指定した項目を検索し、その範囲内で上から数えた数値を返します。

 

 図が示す通り、"名古屋"と入力されたセルが、"札幌"と書かれたセルから数えて何番目かを教えてくれます。そしてまさしくこの「何番目か」をOFFSET関数の「行数」に代入することで、VLOOKUP関数のように、指定した範囲の中から検索条件に一致したデータを検索し、取り出してくれる関数ができあがります。

以上を理解したうえで、先ほどの関数をみると意味が理解できると思います。

=OFFSET($N$4,MATCH(C4,$N$4:$N$14,0)-1,-1)

さて、ここで行数を"-1"している理由ですが、OFFSET関数とMATCH関数の解説を見るとわかる通り、MATCH関数は最初のセルを含んで順番を数えます。一方で、OFFSET関数は基準となるセルを含まずにセルを数えていきます。そのため、MATCH関数でカウントした数から"-1"しないと行数にズレが出てきてしまうのですね。

OFFSET関数とMATCH関数の解説は以上になります。

皆さんも職場でこの関数を使いこなして、エクセル上級者の仲間入りをしましょう！！