VLOOKUP関数で列番号を自動化！MATCH関数を組み合わせた最強の検索術

MATCH関数_応用編

VLOOKUP関数を使っていて、「列番号を数えるのが面倒」「途中に列を挿入したら数式が壊れた」という経験はありませんか？ その悩み、MATCH関数を組み合わせるだけで一瞬で解決できます。

MATCH関数は「指定した値が、範囲内の何番目にあるか」を数字で返してくれる関数です。 これをVLOOKUP関数の「列番号」の部分に組み込むことで、Excelの操作が劇的に快適になります。

練習用ファイルを用意したので、操作しながら学びたい方はこちらからダウンロードしてください。

にゃんこのひと言🐾

INDEX関数とMATCH関数の組み合わせは、Excelの脱・初心者を象徴する強力なテクニックです。こうした関数の「合わせ技」をもっと体系的に学びたいなら、『できるExcel 関数』が非常に分かりやすいですよ🐾

INDEX関数との組み合わせについて

Excelの脱・初心者を象徴するテクニック「INDEX & MATCH」。

以前は左側の検索を行うための必須スキルでしたが、現在はXLOOKUP関数の登場により、一般的に使用頻度は少し落ち着いています。

「INDEX & MATCH」の具体的な使い方については、以下の記事で詳しく解説しています。過去に作成されたファイルの修正などで必要な方は、こちらを参考にしてください。

👉 [検索値から行番号や列番号を検索して抽出する方法]

にゃんこのひと言🐾

引数が多くなると、数式を入力するのも一苦労ですよね。ロジクールマウス(M650)なら、サイドボタンにコピーや貼り付けを割り当てられるので、数式の作成やセル操作が劇的にスムーズになります🐾

なぜVLOOKUP関数にMATCH関数を組み合わせるのか？

VLOOKUP関数の第3引数である「列番号」に、「2」や「3」といった数値を直接入力していませんか？ この「数値指定」には、実務上の大きな弱点が2つあります。

1. 列の挿入・削除に弱い：表の途中に列を1つ増やすだけで、数式が返す値がズレてしまいます。
2. オートフィルが使えない：複数の項目を抽出したいとき、1セルずつ列番号を書き換える手間が発生します。

MATCH関数を組み合わせれば、項目名から自動で列番号を探してきてくれるため、これらのストレスがすべてゼロになります。

VLOOKUP & MATCH の設定手順

VLOOKUP関数とMATCH関数を一緒に組む方法をご紹介します。

VLOOKUP関数では第3引数で検索範囲の何列目を返すか指定しますが、複数の列にオートフィルで反映させたい場合や、参照先の列数に変動があった場合、数値で入力していると第3引数を手動で修正する必要があります。

そこで、MATCH関数を一緒に組むことで、複数列への数式コピーや参照先の列数に変動があった場合でも、常にMATCH関数が現在の列番号を返してくれるので、VLOOKUP関数の第3引数である列番号を修正する必要がなくなります。
列数や位置に変動がある場合は、ぜひMATCH関数と一緒に組んでおきましょう。

F列の商品名をKeyにA～C列を参照して商品番号と単価を返したいので、G2セルにVLOOKUP関数を入れましょう。

VLOOKUP関数を入れたら、第1引数を指定します。
VLOOKUP関数の第1引数は検査値となりますので、ここではF2セルを指定します。

第1引数を指定したら、カンマ , を打って第2引数を指定します。
VLOOUP関数の第2引数は範囲なので、A～C列を指定しましょう。

第2引数を指定したら、カンマ , を打って、いよいよ第3引数に進みます。
通常であれば、ここで列番号として「2」を入力しますが、この列番号をMATCH関数に返してもらうので、第3引数にMATCH関数を入れて Tab キーで確定させます。

Tab キーで確定させたら、MATCH関数の第1引数を指定します。
MATCH関数の第1引数は検査値なので、返したい列の見出しの項目名を選択します。
ここではB列の「商品番号」を返してほしいので、B1セルを選択し行だけ固定の相対参照にします。

第1引数を指定したら、カンマ , を打って、第2引数に進みます。
第2引数は検査範囲なので、第1引数を検索する範囲としてA1～C1セルを選択し絶対参照にします。

第2引数を指定したら、カンマ , を打って、第3引数に進みます。
第3引数は照合の種類となり、ここでは完全一致にしたいので「０」と入力して、MATCH関数のカッコを閉じます。

ここまでがVLOOKUP関数の第3引数となり、これで列番号が指定されました。

VLOOKUP関数の第3引数まで指定が終わったので、カッコを閉じたらカンマ , を打ち、第4引数を指定します。
第4引数は検索方法なので、ここは完全一致の「０」を入力しましょう。

最後にVLOOKUP関数のカッコ閉じて Enter を押せば完成です。

G2セルの関数をH2セルにコピーしてみましょう。
VLOOKUP関数の第3引数を数値で入力した場合、コピーした後に列番号を手入力で修正しなくてはいけませんが、MATCH関数で列番号を指定したので、コピーしても何も修正せず、そのまま正しい値が返ってきました。

あとは、G2～H2セルを選択し、まとめて最終行までオートフィルで反映させればOKです

=VLOOKUP(VLOOKUP関数の第1引数,VLOOKUPの第2引数,MATCH(MATCH関数の第1引数,MATCH関数の第2引数,MATCH関数の第3引数),VLOOKUP関数の第4引数)
=VLOOKUP($F2,$A:$C,MATCH(B$1,$A$1:$C$1,0),0)

まとめ

MATCH関数の応用編として、VLOOKUP関数との最強の組み合わせをご紹介しました。

一見難しく感じる「関数の入れ子（ネスト）」ですが、一度設定してしまえばその後の修正作業は一切不要になります。Excelの実務で「同じ作業を繰り返しているな」と感じたら、ぜひこの自動化テクニックを試してみてください。

---

にゃんこのひと言🐾

「40代からのExcel」は、キャリアを再定義する強力な武器になります
今の職場で「この先、今のスキルのままで通用するのか…」と、ふとした瞬間に不安を感じていませんか？
長年積み上げてきた経験に「最新のExcelスキル」が加われば、市場価値は劇的に上がります。

キャリフトは、そんな40〜50代の「これから」を専門に支えてくれる心強い存在。
今のキャリアをどう活かすか、まずはプロの客観的な意見を聞いてみませんか？

🚀 40代・50代のキャリアコーチングならキャリフト

---