VLOOKUP関数の列番号を自動で変更する方法とは？

この記事では、VLOOKUP関数の3つ目の引数の列番号をオートフィルなどでコピーした時に自動的に列番号を変更させる方法を解説します。

VLOOKUP関数で列番号を数字で指定した場合、オートフィルで計算式をコピーしても列番号の数字は変わらないので計算式をその都度直す必要があります。

そんなときにオートフィルで計算式をコピーできる方法を知っておくと大量のデータを使う際などにとても便利です。

文字で説明すると分かりづらいので、画像を使って詳しく解説します。

---

1．なぜオートフィルでうまくいかないのか

VLOOKUP関数のオートフィルで計算式のコピーがうまくいかない理由として、エクセルでオートフィルを使う場合に「自動的に加算される場合」と「そのままコピーされる場合」の2種類のパターンがあることが原因です。

1-1．オートフィルで加算される例

自動的に加算される例としては「4月」や「8日」など数字を含んだ日付が代表的です。
「4月」からオートフィルを使うと「5月」「6月」「7月」…といった具合に数字が加算されていきます。

また、変わったものでは曜日があります。
「月」と打ったセルからオートフィルをすると「火」「水」「木」…と曜日が自動的に進みます。

1-2．計算式でのオートフィル

計算式をオートフィルした場合、セルの参照先が自動的に加算されます。

例えば、「=SUM(A1:B2)」という計算式を下向きにオートフィルした場合は、
「=SUM(A2:B3)」といった具合にセルの行番号が加算され、横向きにオートフィルした場合には「=SUM(B1:C2)」といった具合にセルの列番号が加算されます。

1-3．VLOOKUP関数でのオートフィル

計算式の自動加算を使えば、VLOOKUP関数で3つ目の引数である「参照する列番号」が加算されてうまくいきそうですが、VLOOKUP関数で自動加算されるのはセルを指定した場合の1つ目と2つ目の引数のみになります。

そのため、3つ目の引数は自動加算されず、オートフィルでVLOOKUP関数をうまくコピペできないという事態がおこってしまいます。

2．列番号を可変させる2つの方法

それではここからは実際にオートフィルで列番号を可変させる2つの方法を解説していきます。

注意点として、1つ目と2つ目の引数にセルを指定する場合は必ず絶対参照をしてください。

2-1．列番号を表に打ち込む方法

1つ目の方法は、追加の関数を使用しないシンプルな方法です。

VLOOKUP関数を使用する表の空いているセルに数字で列番号を横並びで入力（赤枠部分）します。

列番号は、列ごとに実際にVLOOKUP関数で使用する列番号を入力しておきます。

VLOOKUP関数の3つ目の関数を列番号を入力したセルに指定します。

計算式は、

となります。

3つ目の引数にもセルを指定することで、オートフィルで指定したセルの列番号が自動的に加算され、列番号を可変させることができました。

2-2．COLUMN関数を使用する方法

2つ目の方法は3つ目の引数にCOLUMN関数を使用する方法です。

COLUMN関数とは、指定したセルが何列目にあたるかを返す関数です。

例えば「=COLUMN(B3)」と指定した場合、B列は一番左のA列から数えて2番目にあたるので「2」が返されます。

このCOLUMN関数を応用して、3つ目の引数に使用します。

計算式は、

となります。

わかりにくいので、赤字の部分を詳しく解説します。

最初のCOLUMN関数で参照したい列番号のセルを指定（今回は商品名なのでC列のいずれか）することで、C列はA列から3列目という数字を取得します。

2つ目のCOLUMN関数で日付があるセルを絶対参照で指定（今回はA列のいずれか）することで、「C列からA列を引く（3から1を引く）」という計算式が出来上がります。

2つ目のCOLUMN関数にだけ絶対参照をすることによって、オートフィルでコピーをしたときに1つ目のCOLUMN関数の列番号だけが加算され、列番号を可変させることができるようになります。

---

今回の記事では、VLOOKUP関数で列番号を自動で変更させる2つの方法を解説しました。

ただでさえ複雑なVLOOKUP関数に、COLUMN関数を入れることで計算式は複雑になってしまいますが、COLUMN関数自体は引数も一つの簡単な関数なので慣れてしまえば簡単に使用できるようになります。

ぜひマスターして、時短を目指してください！

最後までお読みいただきありがとうございました。