Excel(エクセル)｜VLOOKUPで自動で列番号を取得する方法とは？

今回の記事はExcel(エクセル)｜VLOOKUPの列番号とは？についてお話します。

VLOOKUPの列番号　画像解説

そもそも列番号って何？

VLOOKUP関数の構文は以下の通りです。

列番号とは、参照した範囲の左側から何列目のデータを取り出すのかを指定するもので、通常は「2」「3」「4」などの数字を入力します。

数式をコピーしたら、列番号は修正が必要

挿入したVLOOKUP関数を横方向にドラッグしてコピーした場合について、解説します。

※今回の例では、検索値を複合参照、範囲を絶対参照になっています。

通常ドラッグして横方向にコピーすると、列番号は直接入力しているためコピー元で入力した列番号がそのまま表示されます。

すると、常に同じ結果が表示されます。

解決方法は簡単で、コピー先の列番号を抜き出したいデータがある列番号に変更するだけです。

上記の例では、列番号を「2」から「3」に変更することで、正しい結果が表示されています。

列番号を指定せず自動で取得する方法

VLOOKUP関数を挿入する列が多いほど、修正は手間になりますし、挿入や修正をするたびに左端から何列目なのかを数えるのも効率が悪いです。

そこで、ここからは列番号を数値で直接指定せず、他の関数を使用して相対的な位置を返し、自動で列番号を取得する方法について紹介します。

方法①　COLUMNS関数を使用する

まず、1つ目の方法は、COLUMNS関数を使用する方法です。

COLUMNS関数を使うことで、列の数を数えることができます。

COLUMNS関数の使用が向いているのは、抜き出したいデータが「2，3，4...」と続いている場合です。

下記の例で説明します。

VLOOKUP関数の範囲まで入力しましょう。

列番号の所にCOLUMNS関数を挿入します。

範囲で指定したマスタ表の左上のセルを開始位置として、列番号の数だけ範囲選択します。

範囲指定した開始位置のセルだけ、絶対参照で固定します。

COLUMNS関数をカッコで閉じて、半角カンマ、検索方法を入れたらEnterキーを押して関数を確定しましょう。

マスタ表から2列目を検索した結果が表示されます。

隣のセルに数式をコピーします。

マスタ表から3列目を検索した結果が表示されます。

COLUMNS関数で指定した範囲で、後部の参照だけ変わるので、自動で連続した列番号を参照することができます。

次に、下方向にドラッグして数式をコピーしましょう。

すべてのセルに正しい結果が表示されます。

下方向にコピーすると、COLUMNS関数で参照した範囲も広がりますが、COLUMNS関数では列の数しか結果を返さないので、問題ありません。

参照が広がって気になる場合は、COLUMNS関数で指定した範囲の終わりのセルを行固定すれば、横方向のみ参照が広がります。

COLUMNS関数の組み合わせが向いている例

COLUMNS関数を組み合わせる場合は、結果を表示させたい列の並びが、マスタ表と全く同じように連続している場合です。

COLUMNS関数の組み合わせが向いていない例

結果を表示させたいセルが、連続していない場合は正しい結果を返すことができません。

方法②　MATCH関数を使用する

次に紹介するのは、MATCH関数を使用する方法です。

MATCH関数を使うことで、表の中から相対的な列番号を取得することができます。

MATCH関数では、2列目、5列目、6列目など取得したい列が続いていなくても、自由に取得することができます。

下記の例で説明します。

VLOOKUP関数を挿入し、範囲まで入力します。

列番号の所に、MATCH関数を挿入しましょう。

MATCH関数の検索値を選択します。

コピーしたときに参照がズレないように行固定をしましょう。

固定できたら、続けて半角カンマを入力します。

続いて、範囲を選択します。

今回は、マスタ表の見出しを選択して、参照がズレないように絶対参照で固定します。

固定ができたら半角カンマを入力します。

MATCH関数の検索方法に完全一致の「0」を入力し、MATCH関数をカッコで閉じます。

続いて、VLOOKUP関数の検索方法に完全一致の「0」を入力し、関数を確定させます。

すると、MATCH関数で指定した範囲の中から、同じ見出しを見つけて、対応するデータを取り出すことができます。

数式を隣のセルにコピーしてみましょう。

隣のセルにコピーすると、MATCH関数で検索する条件が「単価」に変わり、それと同じ見出しをマスタ表から探し、列番号を返すことができるので、修正なしで正しい結果を表示できます。

下のセルにも数式をコピーしたら完成です。

たとえ、求めたい内容が変わって、見出しを変更したとしても相対的な位置を返してくれるので、対応した結果を表示してくれます。

MATCH関数の方が万能です

MATCH関数を使えば、COLUMNS関数同様に連続した列番号を取得することができます。

COLUMNS関数では連続していない列番号を取得することは無理でしたが、MATCH関数であれば相対的な位置を検索できるので、正しく結果を表示できます。

さらにワンランク上の効率を求めるなら

今回紹介した例では、マスタ表は通常の表を使用しました。

さらに効率の良いVLOOKUP関数を挿入するなら、マスタ表をテーブルに変換しておくのがオススメです。

テーブルにしておけば、たとえ、マスタ表に行や列が追加された場合でも自動で範囲が拡張されるので、非常に便利です。

1. マスタ表をテーブルにする
2. IF関数を使って空白のエラー処理をしておく
3. 列番号でMATCH関数使用する

この３つのポイントを押さえてVLOOKUP関数を挿入すれば、きれいで効率の良い表が完成します。

Excel(エクセル)｜VLOOKUP関数とは？｜使い方を徹底解説

Excel(エクセル)｜VLOOKUP関数でエラーを返さず空白で返す方法

Excel(エクセル)｜VLOOKUPの検索値を徹底解説

Excel(エクセル)｜VLOOKUPで範囲の選び方を徹底解説

Excel(エクセル)｜VLOOKUPでテーブルを参照する方法

Excel(エクセル)｜VLOOKUP関数で別シートを参照する方法