ExcelのVLOOKUP関数の列番号をCOLUMN関数で自動にする

この記事では、ExcelのVLOOKUP関数の列番号をCOLUMN関数で自動にする方法をご紹介します。

VLOOKUP関数で複数列のデータを抽出したい場合、列番号を手動で修正必要があります。また、列を削除するとエラーが表示されるので修正する手間がかかってしまいます。

以下では、COLUMN関数を使って列番号を自動にして、列を削除してもエラーが発生しない方法をご紹介します。

ExcelのVLOOKUP関数の列番号をCOLUMN関数で自動にする方法

以下では、VLOOKUP関数にCOLUMN関数を組み合わせて列番号を自動にする数式をご紹介します。

以下では、VLOOKUP関数の引数に指定する範囲がシートのA列から始まる場合に、COLUMN関数で列番号を自動で可変させる方法をご紹介します。

上記の画像のようなデータを用意します。この際、VLOOKUP関数の引数に指定するデータを探す範囲が必ずA列になるようにしてください。

F3セルからF5セルに検索値を入力します。

G3セルに「=VLOOKUP($F3,$A:$D,COLUMN(B$2),FALSE)」を入力して、Enterを押します。

VLOOKUP関数の引数の列番号には、COLUMN関数で取得した列番号を指定します。

COLUMN関数は、引数に指定したセルの列番号がA列から数えて何列目であるかを求めます。書式は「=COLUMN([参照])」です。例えば「=COLUMN(D1)」であれば、D列はA列から数えて5番目なので戻り値は5になります。

G3セルに「礼服」が表示されました。

G3セルのフィルハンドルをドラッグして数式をコピーします。

H3セルとI3セルにG3セルの数式がコピーされました。

G3セルからI3セルを選択して、フィルハンドルをドラッグして数式をコピーします。

すべての結果が表示されました。

COLUMN関数を使って列番号を自動で可変させることができました。

上記の画像は、I3セルの数式をコピーしたところ、J3セルからK5セルにエラーが表示されてしまいました。

#REF!エラーは、数式に指定した参照が無効になっている場合に表示されます。主に範囲内のセルなどを削除したり、データが範囲から漏れた場合に表示されます。

COLUMN関数はA列から数えた列番号を返すため、J列（5列目）とK列（6列目）は範囲から漏れていると判断されてしまいました。

以下では、VLOOKUP関数の引数に指定する範囲がB列以降にある場合に列番号を自動で可変させる方法をご紹介します。

I3セルに「=VLOOKUP($H3,$C:$F,COLUMN(D$2)-COLUMN($B$2),FALSE)」を入力してEnterを押します。

1つ目のCOLUMN関数は列番号が可変するので2行目を固定します。2つ目のCOLUMN関数は可変しないのでセル番地を固定します。

VLOOKUP関数のデータ範囲がB列以降にある場合、抽出したい列（例：D2）から範囲の左隣のセル（例：B2セル）を引くと、抽出したい列番号を取得することができます。

抽出したい項目（例：お預かり品）は表の左端から数えると2列目ですが、A列から数えると4列目です。

つまり、「4列目（D2セル）-2列目（B2セル）=2列目」となり、VLOOKUP関数の列番号で「2」が指定されます。

I2セルに「礼服」が表示されました。

I2セルのフィルハンドルをドラッグして数式をコピーします。

J3セルとK3セルにI3セルの数式がコピーされました。

I3セルからK3セルを選択して、フィルハンドルをドラッグして数式をコピーします。

すべての結果が表示されました。

COLUMN関数の使い方を工夫することで列番号を自動で可変させることができました。

VLOOKUP関数では、列を挿入したり削除したりするとエラーが表示されることがありますが、COLUMN関数で列番号を指定している場合は、自動で列が可変するのでエラーを回避することができます。

以下では、実際に列の挿入や削除を試した結果を表示します。

COLUMN関数で列番号を取得している場合は自動でセル参照が可変するため、正しい結果が取得できました。