Excel VLOOKUP関数の使い方②｜工夫次第でこんな事も！

前回の記事にてVLOOKUPの基本的な使い方と、複数条件、ドロップダウンリストとの組み合わせについて解説しました。

今回はその続きで、更なる使い方についてご紹介いたします。今回は列番号にフォーカスします。

VLOOKUP関数の列番号の扱い方

VLOOKUP関数の弱点の一つとして、関数作成後に参照する範囲の列を追加／削除すると、列番号が変わる為に返すデータが変わってしまうということが起きます。
参照範囲のデータの列が後に変更される事が想定される場合にも、予め工夫をしておけば返すデータが意図せず変わってしまうことを防げます。

例えば以下のようにB3:C7の範囲からE3の「みかん」を検索値として価格を返したい場合、=VLOOKUP(E3,B3:C7,2,FALSE)とすれば値（￥100）が取得できます。

しかし、後で参照範囲のデータに加工が生じ、列が増えてしまったら値はどうなるでしょうか？

VLOOKUP関数の計算は一切変更していないですが、列追加に伴い範囲は変わる一方で、値入力した列番号の「2」は変わらないので、結果として2列目の「国産」という値（C5セル）が返ってきてしまいました。
このように元データを加工すると、意図せずに計算結果が変わってしまう場合があります。

このような問題への対応として、加工が生じる可能性がある場合に先んじて対策することが可能です。

B2セルに「　=COLUMN()　」という式を入れ、C2セル、D2セルにも貼り付けました。
この式の意味は、そのセルの列を、A列＝1、B列＝2、というように数字で表現したものです。

続いて、VLOOKUP関数の列番号に数字の値を入れるのではなく、「　D2-B2+1　」という式を入れます。この意味は、「返したい列の番号」 – 「範囲の左端の列の番号」 + 1 (範囲の左端の列を1とカウントする為に足す)ということです。

今回の場合、4(D2セル) – 2(B2セル) +1 = 3、と計算され、B列から3列目にある価格が取得できます。

この式であれば、仮に更にもう1列範囲の途中で列が追加されても、返したい列の番号がそれに伴い増えるので、VLOOKUP関数の列番号の計算結果も1増え、ちゃんと価格を返します。

また、範囲外の左側で列の増減があり範囲の場所がズレても、返したい列の番号、範囲の左端の列の番号が共に増減する為、VLOOKUP関数の列番号への影響はありません。

このように範囲にCOLUMN関数を使い、VLOOKUP関数の列番号を値でなく式を入れることで、範囲データの加工への耐性を高めることができます。

COLUMN関数はVLOOKUP関数の中で違う使い方もできますので、ご紹介いたします。

例えば以下のようにB3:D3にVLOOKUP関数を入れたい場合、検索値や範囲に絶対参照を式をコピペする前提で適切につけたとしても(参照記事)、列番号を値で入れてしまうと可変とならず同じ値が返ってしまいます。

その場合、範囲の列番号と返したいセルの列番号が一致していれば、以下のようにCOLUMN関数を使うことも可能です。必要に応じてCOLUMN()+1などでの調整も可能です。

A3セルをドロップダウンリスト(参照記事)指定したもののみを抜粋でき、それをグラフ化すれば可変グラフなどとアレンジもできます。こういった手法があることを覚えておけば、データまとめの幅が広がると思いますので、是非お試しください！