エクセル・VBA

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

2023年2月21日

Excel(エクセル)|VLOOKUPの列番号とは?

今回の記事はExcel(エクセル)でVLOOKUPを使う時の列番号についてお話します。

列番号は、指定した範囲の左から数えて何列目のデータを取り出すのかを指定するときに使用しますが、挿入するたびに列番号を数えたり、確認したりするのは、なかなか手間です。

そこで今回は、列番号を数字で指定するのではなく、関数を使って自動で列番号を取得する方法について解説します。

ぜひ参考になさってください。

VLOOKUP関数に関する関連記事は下記をご覧ください。

Excel(エクセル)|VLOOKUPで完全一致の使い方

Excel(エクセル)|VLOOKUP関数で近似値の使い方

Excel(エクセル)|VLOOKUP関数とIF関数を使って複数条件を設定する方法


ExcelのVLOOKUPで自動で列番号を取得するのって難しい…

そんなお悩み、当教室のレッスンで一気に解決できます。

✔ 基本操作からしっかり学べる
✔ 「見てマネするだけ」で自然と覚えられる
✔ わからないところはすぐ質問できるから安心

文字を読むのが苦手でも大丈夫。

「実践重視のレッスン」+「元パソコンが苦手だった講師のサポート」で、着実に「感覚としてExcelに慣れていく」ようになります。

詳細は下記をご覧ください。

Excel講座バナー

パソコンが苦手な方向けエクセル講座の詳細はコチラ

VLOOKUPの列番号 画像解説

Excel(エクセル)|VLOOKUPの列番号とは?

Excel(エクセル)|VLOOKUPの列番号とは?


Excel(エクセル)の操作が苦手な方のために、操作する上でのコツをまとめたページを用意しました。

「パソコン教室パレハのLINE友達登録」(無料)で「パソコンに役立つ豪華15大特典」をお送りしております。

Excel(エクセル)の操作はもちろん、パソコンが苦手な方に役立つ特典を15個ご用意しております。

良かったら下記からご登録ください。

ライン登録プレゼント

無料LINE登録はこちら

そもそも列番号って何?

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

=VLOOKUP(検索値, 範囲, 列番号, 検索の型)

検索値何に対応するものを探したいのか、どのデータを使って検索するのか
範囲どの範囲から検索してほしいのか
列番号指定した範囲の左から何列目にあるものを検索するのか
検索の型ピッタリ同じものを探すのか、近いものを探すのか

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

VLOOKUP関数の列番号の説明

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

VLOOKUP関数の列番号の説明

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

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

 

VLOOKUP関数の列番号の説明

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

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

 

VLOOKUP関数の列番号の説明

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

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


ExcelのVLOOKUPで自動で列番号を取得するのって難しい…

そんなお悩み、当教室のレッスンで一気に解決できます。

✔ 基本操作からしっかり学べる
✔ 「見てマネするだけ」で自然と覚えられる
✔ わからないところはすぐ質問できるから安心

文字を読むのが苦手でも大丈夫。

「実践重視のレッスン」+「元パソコンが苦手だった講師のサポート」で、着実に「感覚としてExcelに慣れていく」ようになります。

詳細は下記をご覧ください。

Excel講座バナー

パソコンが苦手な方向けエクセル講座の詳細はコチラ

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

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

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

方法① COLUMNS関数を使用する

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

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

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

下記の例で説明します。

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

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

VLOOKUP関数の列番号の説明

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

 

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

VLOOKUP関数の列番号の説明

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


教室に通う時間がなくてちょっとずつ覚えたいという方向けの「パソコン苦手さん向け通信講座」を作りました。

基本から順番に「実践で覚えるのではなく慣れていく」教材を使います。

「文字を読むのが苦手…」という方でも大丈夫。

動画とセットで感覚的に身につけられます。

パソコン苦手な方の為のパソコン通信講座バナー

パソコンが苦手な方向け通信講座の詳細はコチラ

方法② MATCH関数を使用する

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

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

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

下記の例で説明します。

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

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

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

 

VLOOKUP関数の列番号の説明

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

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

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

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

 

MATCH関数の方が万能です

VLOOKUP関数の列番号の説明 VLOOKUP関数の列番号の説明

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

 

VLOOKUP関数の列番号の説明

VLOOKUP関数の列番号の説明

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

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

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

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

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

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

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

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

Excel(エクセル)が苦手であればパソコン教室で学んでみませんか?

Excel(エクセル)はかなり奥が深く、知識を得ただけは操作が身に付きにくいものです。

そんな時には、弊社のようなパソコン教室を利用するのも一つの手です。

「苦手な方専門のパソコン教室パレハ」のレッスンは、動画ではなく、直接講師に質問できる生レッスンです。

生レッスンでありながらリーズナブルに学べるのが人気の秘密です。

また、苦手な方に特化しているのでパソコンが特に苦手だと言う方には最適です。

パソコンが苦手な方でも苦労なく上達できる仕組みがあります。

詳細は下記をご覧ください。

Excel講座バナー

パソコンが苦手な方向けエクセル講座の詳細はコチラ


教室に通う時間がなくてちょっとずつ覚えたいという方向けの「パソコン苦手さん向け通信講座」を作りました。

基本から順番に「実践で覚えるのではなく慣れていく」教材を使います。

「文字を読むのが苦手…」という方でも大丈夫。

動画とセットで感覚的に身につけられます。

パソコン苦手な方の為のパソコン通信講座バナー

パソコンが苦手な方向け通信講座の詳細はコチラ


Excel(エクセル)の操作が苦手な方のために、操作する上でのコツをまとめたページを用意しました。

「パソコン教室パレハのLINE友達登録」(無料)で「パソコンに役立つ豪華15大特典」をお送りしております。

Excel(エクセル)の操作はもちろん、パソコンが苦手な方に役立つ特典を15個ご用意しております。

良かったら下記からご登録ください。

ライン登録プレゼント

無料LINE登録はこちら

こちらもご覧ください

こちらもぜひご覧ください

エクセル操作情報

Excel(エクセル)|スクロールできない時の対処法を解説

Excel(エクセル)|データの並べ替えをする方法・複数条件の指定

Excel(エクセル)|重複チェック|削除・抽出方法を徹底解説

エクセル(Excel)の罫線を消す方法を解説

Excel(エクセル)|シート保護とパスワード設定方法を徹底解説

Excel(エクセル)|行列(縦横)入れ替える方法|動画

Excel(エクセル) | ウィンドウ枠(列や行)の固定や解除方法 | 複数選択

Excel(エクセル)でセル内で改行する方法|できない時の対処法

エクセルで割り算|四捨五入・切り捨て・切り上げをする方法|動画解説

Excel(エクセル)|テンプレートの作り方

Excel(エクセル)にパスワードを設定(解除)する方法

Excel(エクセル)|MID関数で真ん中の文字を抽出

Excel(エクセル)|曜日を日付から取得して色を自動で変える方法

エクセル|勝手に日付になるのを修正する方法|動画解説付き

Excel(エクセル)で全角を半角に変換して修正する方法|ASC関数

エクセルの印刷範囲|A4サイズに収める方法や設定テクニックを紹介

Excel(エクセル)シートのコピー方法 | ショートカット&エラー対処法

エクセル|文字数をカウントするLEN関数の使い方

Excel(エクセル)|文字の縦書き・横書きの設定・解除方法

Excel(エクセル)ファイルをPDFに変換する方法をわかりやすく解説

Excel(エクセル)|半角のデータを全角に一括変換できるJIS関数

Excel(エクセル)|名前の定義の設定方法と使い方を解説

Excel(エクセル)|日付を入れて曜日を自動で出す|表示形式の設定方法

Excel(エクセル)|TEXT関数|日付から曜日を表示する方法

Excel(エクセル)を勉強する方法|効率よい方法教えます

Excel(エクセル)で掛け算する方法|列ごとの方法|初心者向け|動画付

Excel(エクセル)|チェックボックス(レ点)の作成や削除する方法

Excel(エクセル) | コメントを表示して印刷する方法を解説

Excel(エクセル)でハイパーリンク (URL)の貼り付けを設定する方法

Excel(エクセル)|入力規則を利用しデータを効率よく入れる方法を徹底解説

エクセル|検索・置き換えのやり方|複数の文字や数字の置換方法

Excel(エクセル)|読み取り専用の設定と解除方法

Excel(エクセル)でヘッダー・フッター(ページ番号)を作成・編集方法

Excel(エクセル)で文字列の不要なスペースを削除する方法

Excel(エクセル) | 取り消し線の付け方(ショートカット)を解説

Excel(エクセル)|図形を作成・追加する方法

Excel(エクセル)で引き算する方法|関数やエラー対処法

エクセルで年齢を計算する方法|関数で自動更新

Excel(エクセル)で印刷範囲を設定する方法|一部だけ・1ページにぴったりなど解説

Excel(エクセル)|表の作り方の手順を徹底解説|初心者向け

Excelで文字列を"&"や関数で結合する方法|空白や記号の追加

Excel(エクセル)で時間を計算する方法|経過・合計・24h以上の表示

Excel(エクセル)で四捨五入する方法|ROUND関数の基本から応用まで

Excel(エクセル)|ROUNDDOWN関数で切り捨てする方法|基本から応用

エクセル|COUNT関数の使い方と個数を数えるその他の関数

エクセル|斜線を引く方法と消す方法|分割したセルに文字を入力する裏技

Excel(エクセル)|PHONETIC関数でふりがなを表示する方法|修正・エラー原因

Excel(エクセル)で日付を自動で表示・更新する方法|役立つ関数6選

エクセルで効率よく日付を入力する方法|ショートカット・関数

Excel(エクセル)でリンクを解除する方法|ショートカットキーも紹介

Excel(エクセル)で文字列を抽出できる関数と関数の組み合わせ方法

Excel(エクセル)でバージョンを確認する方法

Excelで不要な空白行をまとめて一括削除する方法

Excel(エクセル)の「スピル」機能って何?使い方とエラーの解決方法

エクセルで電子印鑑を作成・使用する方法|メリットとデメリット

Excel(エクセル)で平均を求める3つの方法|計算式なしの裏技も紹介

Excel(エクセル)でシート移動ができるショートカット|時短ワザ

Excel(エクセル)|基本・条件指定・計算式の裏ワザで合計を出す方法

Excel(エクセル)の共有設定と解除方法

Excel(エクセル)計算式や関数の入力方法|基本から応用まで

Excel(エクセル)のフィルターの設定方法|使い方の基本から解説

Excel(エクセル)で絶対値を表示する方法|ABS関数とその他2つの方法

Excel マクロ|組み方・使い方・ボタンの作り方まで徹底解説

エクセル|表をテーブルに変換で作業効率化|解除方法まで徹底解説

Excel(エクセル)で文字列に「ふりがな・ルビ」を付ける・修正する方法

メールの画像

メールはこちら

ラインの画像

LINEはこちら

電話の画像

お電話はこちら