Excel(エクセル)のVLOOKUP関数(ブイルックアップ)は表を縦方向検索して、検索値を抽出する大変便利な関数です。
しかし、VLOOKUP関数を複数のセルで利用する際、引数「列番号」をいちいち切り替えて変更するのは面倒です。
そんな時、「列番号」の変更を自動で切り替える２つの方法あります。
COLUMN関数と一緒に使う方法と、MATCH関数と一緒に使う方法になりますので、関数の使い方も合わせてご紹介します。
VLOOKUP関数の使い方をまだ理解出来ていない方はこちらの記事から確認してね！
VLOOKUP関数の「列番号」を自動で切り替える方法
- VLOOKUP関数とCOLUMN関数を組み合わせる
- VLOOKUP関数とMATCH関数を組み合わせる
列番号を変える手間とは
まず、「VLOOKUP関数の列番号を変える手間」とはどういうことか下の表を使って紹介します。
下の表(B3:E15)から、商品番号「25」の商品カテゴリを抽出するために、H4セルに以下のVLOOKUP関数を挿入します。
=VLOOKUP($G$4,$B$4:$E$15,2,FALSE)
この時、３つ目の引数「列番号」は、表の左から２行目にあるので「２」を指定します。
次に、表内から商品番号「25」の商品名を抽出するために、I4セルにVLOOKUP関数を挿入するために、H4セルのVLOOKUP関数をコピーします。
そうすると、関数は以下のとおりとなり、３つ目の引数「列番号」は「２」のままです。
=VLOOKUP($G$4,$B$4:$E$15,2,FALSE)
そのため、手動でVLOOKUP関数の列番号「２」を「３」に変更する必要があります。
列番号を変更すると、商品番号「25」の商品名を正しく抽出することが出来ました。
このように表内から複数の値を抽出するためにVLOOKUP関数を複数のセルに利用する場合は列番号の修正に時間がかかります。
以降の見出しで列番号の修正を自動で可変する方法を紹介します。
COLUMN関数の使い方
COLUMN関数を使うことでVLOOKUP関数の列番号を自動で入力することが可能ですが、まずはCOLUMN関数について紹介します。
COLUMN関数は、指定したセルの列番号を表示することが出来ます。
＝COLUMN(セル番号)
例えば「＝COLUMN(B2)」とすると、B2セルは２列目にありますので、COLUMN関数の値は「２」となります。
非常にシンプルな関数ですよね。
また、セル番号を指定せずにCOLUMN関数を使うと、関数を挿入したセルの列番号が返されます。
感のいい人は、VLOOKUP関数にどうやってCOLUMN関数を利用するかイメージ出来た人もいるかもしれませんね。
次の見出しで組み合わせの方法をご紹介します。
VLOOKUP関数とCOLUMN関数で列番号を自動で変更する方法
それでは、上の図の赤枠のセルに、VLOOKUP関数とCOLUMN関数を用いて、検索範囲から商品番号「25」の「商品カテゴリ」「商品名」「値段」を表示する方法をご紹介します。
手順１．VLOOKUP関数の第１、第２引数を指定
「商品カテゴリ」を表示したいH4セルに、VLOOKUP関数を挿入し、１つ目の引数「検索値」と２つ目の引数「検索範囲」を指定します。
引数は、右のセルにコピー出来るよう絶対参照(＄)で固定しておきましょう。
=VLOOKUP($G$4,$B$4:$E$15
手順２．VLOOKUP関数の第３引数にCOLUMN関数を挿入
次に、３つ目の引数「列番号」にCOLUMN関数を挿入します。
「商品カテゴリ」列は検索範囲の左から２列目にありますので、引数「２」と入力したいところですが、COLUMN関数の結果を「２」で表示させるために、COLUMN(B3)と入力します。
セルをB3で指定していますが、B1でもB2でもB4でもB列であればどのセルでもOKです。
=VLOOKUP($G$4,$B$4:$E$15,COLUMN(B3),
手順３．VLOOKUP関数の第４引数を指定
最後の４つ目の引数「検索方法」に「FALSE」を挿入して、「Enter」キーで数式を完成させると、商品番号「25」の「商品カテゴリ」の「薬」が表示されます。
=VLOOKUP($G$4,$B$4:$E$15,COLUMN(B3),FALSE)
手順４．VLOOKUP関数を隣のセルにコピペ
手順３で完成したVLOOKUP関数を右のセルにコピペします。
列番号を変更しなくてもVLOOKUP関数の結果は正しく表示されます。
ただし、この方法は、検索範囲の項目の並び順とVLOOKUP関数を挿入するセルの項目の並び順が同じことが利用出来る条件となります。
VLOOKUP関数とMATCH関数で列番号を自動で変更する方法
MATCH関数を使うことでもVLOOKUP関数の列番号を自動で入力することが可能です。
下の図の赤枠のセルに、VLOOKUP関数とMATCH関数を用いて、検索範囲から商品番号「25」の「商品カテゴリ」「商品名」「値段」を表示する方法をご紹介します。
MATCH関数は、セルの相対的な位置を数値で表示してくれる関数です。MATCH関数の詳しい使い方は下のリンク記事を参照してください。
それでは、VLOOKUP関数にMATCH関数を組み合わせる手順を紹介していきます。
まず、「商品カテゴリ」を表示したいH4セルに、VLOOKUP関数を挿入し、引数の”検索値”と”検索範囲”を指定します。
引数は、右のセルにコピー出来るよう絶対参照(＄)で固定しておきましょう。
=VLOOKUP($G$4,$B$4:$E$15
次に、３つ目の引数「列番号」に以下のMATCH関数を挿入します。
=VLOOKUP($G$4,$B$4:$E$15,MATCH(C3,$B$3:$E$3,0)
関数が長くなり一見複雑に思えるかもしれませんが、MATCH関数の結果が「２」になるのはお分かりになりますか？
MATCH関数の検索範囲を表の項目行を指定し、検索値を検索したい項目名を指定しています。
最後の４つ目の引数「検索方法」に「FALSE」を挿入して、「Enter」キーで数式を完成させると、商品番号「25」の「商品カテゴリ」の「薬」が表示されます。
=VLOOKUP($G$4,$B$4:$E$15,MATCH(H3,$B$3:$E$3,0),FALSE)
完成したVLOOKUP関数を右のセルにもコピペします。
そうすると、列番号を変更しなくてもVLOOKUP関数の結果は正しく表示されます。
(おまけ)HLOOKUP関数の行番号も自動化
VLOOKUP関数の兄弟関数ともいえるHLOOKUP関数は、表を横方向に検索し、検索値を抽出する関数です。
この関数の３つ目の引数『行番号』も、VLOOKUP関数と同様に自動で変更が可能です。
＝HLOOKUP(検索値,検索範囲,行番号,[検索方法]）
以下の記事で詳しく紹介していますので、興味のある方は参考にしてみてください。
(おまけ)表からクロス抽出するテクニック
表から縦方向に検索し抽出するVLOOKUP関数と、横方向に検索するHLOOKUP関数はExcel(エクセル)の定番関数です。
表を縦方向(列)と横方向(行)に検索する機能をそれぞれ持っていますが、特定の行と列がクロスするセルの値を自動で取り出す関数の使い方も存在しています。
以下の記事で詳しく紹介していますので、興味のある方は参考にしてみてください。
