VLOOKUP と MATCH が奏でる名簿検索のシンフォニー: 数値入力なしで効果的な列指定のテクニック

今回は、 VLOOKUP 関数を使うときに煩雑な 列指定 を、数値で指定するのではなく、 MATCH 関数で列の項目名を検索して、使う方法について解説します。

横の列数が多い表だと、列の番号を見つけようとすると数えるのが大変です。また、表の列の位置を変更したいときに、式も変更が必要となります。
この方法を使うと、どんなに列数が多くて横に長い表でも、苦労して数えることなく、表の列の順番を変更しても基本的には関数を書き直すことなく、 VLOOKUP 関数を使うことができます。

範囲の列が多くて列番号を左から123、、、と数えるのが大変だし間違えやすいから嫌だ
せっかく作ったのに、列の順序を入れ替える度に、何度も123、、、と数えなくちゃで大変だ
関数をメンテナンスするときに、式が間違っていないか確認するのが本当に大変

このように、VLOOKUP 関数を使う時、第三引数の列番号を数えるのが、どうもスマートではなくて、しっくりきませんね。

列番号が二番目、三番目くらいならぱっと見でわかりますが、列が多くなってくると何回も数え直したり、何回も間違ったりでストレスを感じてとても嫌です。

そんなときには、 MATCH 関数がとても役に立ちます。使いはじめると仕事がスマートになりますよ。

MATCH 関数の使い方
MATCH 関数で何番目かを調べる
VLOOKUP 関数の列番号を MATCH 関数で取得する
式を整えます
まとめ
関連する記事

MATCH 関数の使い方

まずは、 MATCH 関数の仕様を見てみましょう。

書式
MATCH(検査値, 検査範囲, [照合の型])
MATCH 関数の書式には、次の引数があります。
検索範囲 必ず指定します。検索するセルの範囲を指定します。
照合の型 省略可能です。 -1、0、1 の数値のいずれかを指定します。
照合の型には、検査範囲の中で検査値を探す方法を指定します。この引数の既定値は 1 です。
Microsoft サポート MATCH 関数　から引用

MATCH 関数は、検索範囲の中のどこ（何番目）に検査値（見つけたい値）があるかを返す関数です。

= MATCH ("名前", {"番号" , "名前" , "性別"}, 0)

関数の第二引数には検索範囲（または配列）を指定します。 VLOOKUP で実際に使うときは、検索範囲を 表のタイトル行（例えば、C1:E1とか）で使います。この例では、｛”番号”, “名前”, “性別”｝の範囲から”名前”の位置を検索し、範囲の二番目に”名前”がありますので、2が返されます。

このように、検索で抜き出したいタイトルの名称で検索して列番号が求められるので、VLOOKUP関数の列指定に最適なんです。

MATCH 関数で何番目かを調べる

まず、図の赤の枠で囲んだ部分を見てください。

MATCH関数の第二引数（赤文字）検索範囲はF2:H2となっており、F2～H2の範囲の何番目に第一引数 B2セルの中身（”名前”）があるかを調べています。結果は1となります。

この番号を直接 VLOOKUP 関数の第三引数（列番号）にそのまま利用します。

この例では、検索範囲が3列しかありませんが、実際に使う表は、もっと列数が多くて、抜き出したい列はその中で飛び飛びに存在している事が多いですよね。

名簿のたくさんある列（項目）の中から、「名前」や「部署」を抜き出したい、などのとき、数えずに列の番号が取得できるというのは、とても効率的で助かります。

VLOOKUP 関数の列番号を MATCH 関数で取得する

では、実際に MATCH 関数を使って、VLOOKUP 関数を書いてみます。

いつもは数値で指定していた、第三引数を先ほどの MATCH 関数に変更するだけでできます。

先程の例でB3のセルに、以下の式を入力します。（関連記事:IFERROR関数）

=IFERROR(VLOOKUP($B5,$L$3:$O$1000, MATCH(C$4, $L$2:$O$2, 0), FALCE), "")

VLOOKUP 関数の第三引数（赤文字にしています）は、MATCH関数でL2~O2の中からC4の”名前”を検索した結果としています。
B5セルの数値（例では1）を L列の中で検索し、L3で一致した行を見つけ、その”名前”の列番号（2）にある”田中太郎”を抜き出します。

式を整えます

最後に、式を整えておきます。

これは、無理にやらなくても動作には問題ありませんが、自分で後から見たときにすぐに思い出せるし、チームの他の人も分かりやすくなり流用しやすいですし、修正時に誤りも発生しにくいなど、無駄が発生しにくくなるということで、この手順をしておくことをお勧めします。　見て気持ちが良いですよ。

ではこのままだと、数式にアドレスの指定がたくさん有って見にくいので、式が間違っていないかチェックしづらいです。　その対策として、範囲のアドレス指定に名前定義を使って名前を定義しておきます。VLOOKUP 関数の対象範囲と、MATCH関数の検索範囲を定義して見やすくしておきます。

（名前定義の関連記事:名前定義）

Alt M M D と入力して名前定義のダイアログを開きます。VLOOKUP関数用には、名前(N)を”名簿範囲” 参照範囲(R)をL3:O1000の絶対参照として定義します。　また、MATCH関数用にもう一つ、名前(N)を”ラベル範囲” 参照範囲(R)をL2:O2の絶対参照も定義しておきます。

早速先ほどの数式を定義した名前の”名簿範囲”および”ラベル範囲”で書き直しましょう。

=IFERROR(VLOOKUP($B5,名簿範囲,MATCH(C$4,ラベル範囲,0),FALSE),"")

どうですか、これで、大分スッキリして見やすくなりました。この式を、C5:E9の範囲にコピペします。その時一点だけ注意して欲しいのは、罫線などの書式を崩さないように、条件を指定して”数式”のペーストをします。

こうする事で、書式が維持されたまま数式のみのペーストができました、完成です。

まとめ

今回の記事では、VLOOKUP関数を使用するに当たって、ちょっと不便だなと感じる列番号の指定を、手間をかけずに、意味のある関数で代替して行う内容でした。

得られる効果は絶大ですから、どんどん使っていただき、マスターして下さい。

MATCH 関数の使い方

MATCH 関数で何番目かを調べる

VLOOKUP 関数の列番号を MATCH 関数で取得する

式を整えます

まとめ

関連する記事

コメント