ExcelでVLOOKUP関数の列番号を数値指定せずMATCH関数を利用する

この記事は公開から2年以上経過しています。

こんにちは、チーフクリエイターのSHOJIです。

Excelを使った業務で、最も利用されていると言っても過言ではないVLOOKUP関数。
簡単に説明すると、元になる表内を検索して該当する値を別の表に転記するものです。

ですが、よく使われるからこそ思いもよらないエラーが生じてしまったことってありませんか？

そこで今回のExcelの豆知識は『VLOOKUP関数の列番号を数値指定せずMATCH関数を利用する』と題して、VLOOKUP関数を利用した際によくある問題を回避する方法についてご紹介します。

• \\　目 次　//
• VLOOKUP関数の基本的な使い方
• VLOOKUP関数でよくある問題
• 相対的な位置を返すMATCH関数
• VLOOKUP関数の列番号をMATCH関数で入力
• あとがき

VLOOKUP関数の基本的な使い方

まず、VLOOKUP関数の設定としては
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
と、このようになります。

例えば下図のように、社員番号に対応する社員氏名や所属先を取り出すケースで見てみましょう。

右のテーブルから所属先の値を取り出すため、セルD3に設定するVLOOKUP関数は次のようになります。（※右のテーブルには「テーブル1」と名前を付与しています）

=VLOOKUP($B3,テーブル1[#すべて],3,FALSE)
・検索値　 = 社員番号($B3)
・範囲　　 = テーブル１全域
・列番号　 = 3
・検索方法 = FALSE(完全一致)

VLOOKUP関数でよくある問題

参照先の表（テーブル）に列を挿入したことで列番号がズレてしまい、取得値が変わってしまった経験ありませんか？

これは、VLOOKUP関数の引数として列番号を数値で指定することに起因しており、VLOOKUP関数の弱点でもあります。

このように、参照先のデータに増減が生じることで関数を修正する必要があり、とても面倒です。
 

相対的な位置を返すMATCH関数

そこで登場するのがMATCH関数です。
MATCH関数は、検索値の範囲内における相対的な位置（列番号）を返してくれます。

例えば下図のように検索値を「所属」とする場合、右テーブルの見出しにおける列番号を取得するケースで見てみましょう。

右テーブルの見出しにおける列番号を取得するため、任意のセル(D1)に設定するMATCH関数は次のようになります。（※右のテーブルには「テーブル1」と名前を付与しています）

=MATCH(D$2,テーブル1[#見出し],0)
・検索値　 = 所属(D$2)
・範囲　　 = テーブル１見出し
・検索種類 = 0(完全一致)

このように、MATCH関数で列番号を取得することができるため、VLOOKUP関数における列番号の入力を数値ではなくMATCH関数に置き換えることが可能になります。
 

VLOOKUP関数の列番号をMATCH関数で入力

前述で設定したMATCH関数をコピーして、VLOOKUP関数の引数「列番号」に入力した数値を変更するようペーストします。

=VLOOKUP($B3,テーブル1[#すべて],MATCH(D$2,テーブル1[#見出し],0),FALSE)

この設定によって、参照先の表（テーブル）に意図せず列が挿入されてしまっても取得される値が変わってしまうといった問題に悩まされる心配がなくなります♪

あとがき

VLOOKUP関数で特に悩ましいのが、取得対象となる引数「列番号」の指定方法です。前述のように参照する表の変更に伴い、列番号もその都度修正する必要がありました。

そこで今回ご紹介した「列番号を数値ではなくMATCH関数で指定する」ことで、問題を回避したり、表の変更に伴う列番号修正の必要がなくなることがお分かりいただけたかと思います。

…なんですが、2019年に新関数として発表された「XLOOKUP関数」を使えば、こんな問題も実は簡単に解消されるんです！

縦方向に検索するVLOOKUP関数、横方向に検索するHLOOKUP関数は、ともに特定のデータに対応する値を４つの引数をして取り出すもので、引数の指定も面倒で使い分けも必要でしたが、新関数のXLOOKUP関数は３つの引数で縦方向も横方向もこれひとつでカバーしてくれる優れもの！

そこで次回のExcelの豆知識では、この便利なXLOOKUP関数についてご紹介したいと思います。
乞うご期待♪
 

• 前の記事へ

  新社会人は知っておきたい、目標達成のためのPDCAサイクルとは？

• 次の記事へ

  インフォコネクト図書館：僕らはSNSでモノを買う（飯高悠太）