Aさん VLOOKUP関数は便利なのは実感していますが、横方向にコピペするとイチイチ「列番号」を直すのが面倒ですが、何か良い方法はないでしょうか？
森田 「列番号」はなぜか数式中に直接数値を入れてしまう方が多いですね。一番おすすめなのは、「列番号」の引数にMATCH関数を組み合わせることです。
こうすることで、コピペはもちろん楽になりますし、参照先のリストの列の追加や削除を行ってもVLOOKUP関数の結果が狂わないですよ！
では、詳細をわかりやすく解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- データの転記・集約作業が多い人
- エクセルを使う頻度が高い人
- 事務職を目指している人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
同じ「検索値」の複数列のVLOOKUP関数を1回のコピペで済ませたい！
言葉だけだといまいち伝わっていない方もいると思いますが、要は以下のような状態を指しています。
この場合、同じ列であれば同じ数式をコピペすれば良いですが、違う列になるとVLOOKUP関数の引数「列番号」をいちいち手修正する必要がありますね。
これって列が多ければ多いほど面倒になります。
引数「列番号」にMATCH関数を組み合わせる！
VLOOKUP関数の引数「列番号」をエクセル側で計算してくれるようにするために、MATCH関数を活用します。
MATCH関数とは？
このMATCH関数はVLOOKUP関数と同じく検索/行列関数の一種で、任意のキーワードが、指定した行（列）の中で左（上）から何番目かを数値で返してくれる機能を持っています。
MATCH(検査値,検査範囲,[照合の種類])
試しに、このMATCH関数を単体で使ってみるとイメージが湧きやすいと思います。
たとえば、次の表のA1~E1の中で、”名”という値は左から何番目かをMATCH関数を用いて求めてみましょう。
引数名はちょっと違いますが、MATCH関数はVLOOKUP関数と使い方は似ています。
MATCH関数の引数「検査値」はVLOOKUP関数でいうところの引数「検索値」といっしょでキーワードを入力します。
今回は、定数として“名”を数式中に入力しています。
続いて、引数「検査範囲」はVLOOKUP関数でいうところの引数「範囲」といっしょで、今回はA1~E1セルが対象なので”A1:E1”を入力しています。
最後に引数「照合の種類」はVLOOKUP関数でいうところの引数「検索方法」といっしょで、今回は完全一致にしたいので”0”を指定しました。
結果、MATCH関数を挿入したB9セルには”3”という計算結果になりましたね。
実際、A1~E1の中で、”名”という値は左から3番目にあることが確認できます。
組み合わるとはどういうことか？
組み合わせるとは、このMATCH関数とVLOOKUP関数の引数「列番号」のところに入れるわけです。
こうして2つの関数を組み合わせて使うことで、VLOOKUP関数が参照している引数「範囲」に列を追加・挿入しても自動的に該当の「列番号」を計算してくれます。
これで、いちいち引数「列番号」の手直しが不要になって便利ですよ。
もちろん、B2セルのVLOOKUP関数の数式を右方向へコピペしても自動的に引数「列番号」を計算してくれます。
サンプルファイルで練習しよう！
では、実際に今回のVLOOKUP関数とMATCH関数の組み合わせテクニックを使ってみましょう！
サンプルの条件
今回の題材は次のとおりです。
「名簿」というシートに生徒の出席番号・氏・名・誕生月・誕生日が一覧表になっています。
そして、もうひとつ「検索」シートがあり、すでにA2セルには出席番号”A-01”が入力済みです。
この出席番号”A-01”をキーワードに、B～E列に該当の氏・名・誕生月・誕生日をVLOOKUP関数で転記しましょう。
実際に操作しよう！
可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。
サンプルファイル_VLOOKUP関数_列番号②
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ファイルを開いたら次の手順を実施してください。
- 「検索」シートを選択
- B2セルへ「=VLOOKUP($A2,名簿!$A:$E,MATCH(B$1,名簿!$A$1:$E$1,0),0)」を入力
※赤字の部分をコピーして貼り付けてください。 - B2セルをコピー
- C2～E2へ貼り付け（ペースト）
上記手順を行った結果は「検索 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じなっていればOKです。
A2セルの出席番号を他の番号にプルダウンを切り替えた際に、ちゃんと連動してすべての値が変わるかも確認しましょう。
さらに、MATCH関数の効果を確かめるべく、「名簿」シートのB列以降に列の追加や削除をしてみてください。
それでもVLOOKUP関数の戻り値（返り値）は変わらないですよ！
ポイントの解説
最後に今回のテクニックの4つのポイントについて解説していきましょう。
【POINT1】主キーの設定
このテクニックに限らずVLOOKUP関数を使う際のマストなポイントです。
こちらは検索を行う対象の表に「主キー」が入ったものを準備しておきましょう。
この「主キー」とは、データを固有のものと識別するための値であり、かつ一意（他データと重複していない）である必要があります。
今回の例でいえば、”出席番号”が該当しますね。
こういった「主キー」があると、VLOOKUP関数で意図しない戻り値（返り値）になることを防ぐことができます。
【POINT2】MATCH関数の引数「検査値」となる値は見出し行に用意しておく
MATCH関数の引数「検査値」は先の説明のとおり、検索するためのキーワードを指定します。
ここで、おすすめなのは、VLOOKUP関数の引数「範囲」で指定している表の見出し部分をMATCH関数の引数「検査値」で指定することです。
今回のサンプルのように、検索先と元で共通の見出し名をワークシート上で用意しておくと使いやすいですよ。
【POINT3】MATCH関数の引数「検査範囲」はVLOOKUP関数の引数「範囲」の列とそろえる
MATCH関数の戻り値（返り値）が狂わないように、MATCH関数の引数「検査値」とVLOOKUPの引数「範囲」の参照する列はそろえてください。
今回のサンプルでいえば、お互いに「名簿」シートのA~E列にしています。
ちなみに、MATCH関数は見出し行を指定したいので「A1:E1」を、VLOOKUP関数は表含めてすべて指定したいので「A:E」のように列単位で指定していますよ。
【POINT4】絶対参照・相対参照の設定
これは、数式を扱う上で基礎中の基礎ですが、コピペを一括で行うために必要です。
今回セル参照しているのはVLOOKUP関数とMATCH関数を合わせて4つの引数ですが、それぞれ以下の意図があって絶対参照・相対参照を設定しています。
すべて「検索」シートのB2セルをもとに説明していきますね。
VLOOKUP関数－引数「検索値」
今回は「$A2」と列のみ絶対参照にしています。
これは、A列にVLOOKUP関数のキーワードとなる”出席番号”があり、B~E列に入れるVLOOKUP関数の「検索値」は同じ行なら一緒なため、列のみ固定にしているわけです。
VLOOKUP関数－引数「範囲」
今回は「名簿!$A:$E」と行列ともに絶対参照にしています。
これは、どのVLOOKUP関数も参照する範囲は「名簿」シートのA～E列のため、行列どちらも固定にしているわけです。
MATCH関数－引数「検査値」
今回は「B$1」と行のみ絶対参照にしています。
これは、1行目にMATCH関数のキーワードとなる見出し名があり、仮に下方向にVLOOKUP関数＋MATCH関数をコピーしていった場合でも、すべてのVLOOKUP関数内のMATCH関数がそれぞれと同じ列の1行目を参照できるよう、行のみ固定にしているわけです。
MATCH関数－引数「検査範囲」
今回は「名簿!$A$1:$E$1」と行列ともに絶対参照にしています。
これは、どのMATCH関数も参照する範囲は「名簿」シートのA1～E1セルのため、行列どちらも固定にしているわけです。
絶対参照・相対参照のまとめ
上記の説明は実際にワークシート上にさらに複数行のVLOOKUP関数が増えるとわかりやすいです。（画像をクリックすると拡大します）
さいごに
MATCH関数を活用すると、今回のようにVLOOKUP関数の引数「列番号」をエクセル側で自動的に調整してくれるようになるため、非常に便利なテクニックです。
私も日常的に使用する頻度は非常に高く、だいぶお世話になっている関数のひとつですね。
実際、MATCH関数はVLOOKUP関数以外にもOFFSET関数やINDEX関数などの中上級者向けの関数を覚えていく際にも役立ちます。
ぜひVLOOKUP関数と併せて使えるように練習することをおすすめします。
こういったテクニックに加え、その他のVLOOKUP関数関連のテクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 MATCH関数は正直最初とっつきにくかったですが、VLOOKUP関数と組み合わせて使えるようになると、もうこれなしでは生きていけないくらい便利です（笑）
ちゃんと理解すると、ほんとうにVLOOKUP関数と引数の設定方法も似ているので、何度も試していく中で絶対覚えられるのでがんばって覚えてくださいね！
