【関数】同じ数式で複数列の転記ができる「VLOOKUP」と「MATCH」の組み合わせテクニック
AさんAさん

VLOOKUP関数は便利ですが、複数列を転記したい場合にコピペしたVLOOKUPの引数「列番号」をいちいち直すのが面倒です。。
何か良い方法はないでしょうか?

森田森田

VLOOKUPの数式を複数列転記させる方法はいろいろありますが、一番おすすめなのは、MATCH」と組み合わせるテクニックですね!
では、VLOOKUP+MATCHの組み合わせテクニックについて解説していきますね。

はじめに

この記事はVLOOKUPとMATCHの詳細を把握していることが前提です。

参考記事

VLOOKUPとMATCHの使い方の詳細は以下の記事をご参照ください。


VLOOKUPでの複数列の転記は引数「列番号」の手修正での対応が一般的

VLOOKUPで同じ主キーを基準に、別表の複数列を転記したい場合、引数「列番号」を定数で指定していると、列ごとに手修正が必要となります。

転記する列数が少なければ問題ないですが、列数が増えれば増えるほど面倒ですし、ヒューマンエラーが起きるリスクも上がります。

よって、複数列の転記の場合、この手修正が不要になるように、ベースとなるVLOOKUPの数式をコピペで使い回せるようにしておきましょう。

VLOOKUPの引数「列番号」にMATCHを組み合わせれば、同じ数式で複数列の転記が可能!

そのためには、VLOOKUPの引数「列番号」にMATCHをネスト(代入)すると良いです。

MATCHで各フィールド名がマスタ上で何列目かを自動計算でき、その結果がVLOOKUPの引数「列番号」となります。

これで、複数列の転記であっても、ベースとなるVLOOKUPの数式をコピペで使い回すことが可能となります。

【注意】VLOOKUP+MATCHの要注意ポイント3

VLOOKUP+MATCHの組み合わせテクニックにおいて、3つの要注意ポイントがあります。

POINT12つの表のフィールド名を一致させておく

1つ目のポイントは、元データ(VLOOKUPの数式側)と転記したいデータ(マスタ)の2つの表のフィールド名を一致させておくことです。
英字の大文字↔小文字は許容されるが、それ以外の表記ゆれは注意が必要(英数字の全角↔半角、スペースや改行の有無等)。

これが一致していないと、MATCHで取得する列番号が誤るかエラー値となり、連動してVLOOKUPの数式自体も誤った結果かエラー値になってしまうので、ご注意ください。

POINT2VLOOKUPの引数「範囲」とMATCHの引数「検査範囲」の列を一致させる

2つ目のポイントは、VLOOKUPの引数「範囲」とMATCHの引数「検査範囲」で参照する列を一致させることです。

それぞれ参照するマスタの起点と終点の列が一致していればOKです。

なお、参照するマスタがテーブルの場合は、次の通りです。

それぞれの参照範囲の列が一致していないと、MATCHで取得する列番号が誤るかエラー値となり、連動してVLOOKUPの数式自体も誤った結果かエラー値になってしまうので、ご注意ください。
マスタがセル範囲かテーブルかでVLOOKUP側の参照範囲が異なっていることに注意。

POINT3MATCHの引数「検査値」は複合参照(行のみ絶対参照)にする

3つ目のポイントは、MATCHの引数「検査値」は複合参照(行のみ絶対参照)にすることです。

特に、VLOOKUPの数式をセットする表がテーブルの場合、引数「検査値」をセル選択で指定すると「注文テーブル[[#見出し],[商品名]]」のような構造化参照になってしまいます。

この状態だと、せっかくMATCHと組み合わせても列が固定されてしまい、コピペで使い回せません。

よって、MATCHの引数「検査値」は「D$1」等の複合参照(行のみ絶対参照)を直接入力しましょう。

これで、コピペ後にMATCHの引数「検査値」の参照セルが横方向へスライドし、複数列の転記に対応できます。

参考記事

絶対参照/相対参照の詳細については以下の記事をご参照ください。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_VLOOKUP+MATCH.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ブックを開いたら、次の手順を実施してください。

  1. 関数を挿入するセルを選択
    ※今回はD2セル
  2. =vl」等と入力
  3. サジェストから「VLOOKUP」を選択し、「Tab」キーで確定
  4. 主キーのセルを選択
    ※今回はC2セル(列のみ絶対参照)
  5. コンマ(,)を入力
  6. 転記したいデータのシートへ移動
    ※今回は「商品マスタ」シート
  7. 転記したいテーブル/セル範囲を選択
    ※今回はAC列(絶対参照)
  8. コンマ(,)を入力
  9. mat」等と入力
  10. サジェストから「MATCH」を選択し、「Tab」キーで確定
  11. 元データのシートへ移動
    ※今回は「注文テーブル」シート
  12. 検査値にしたいセルを選択
    ※今回はD1セル(行のみ絶対参照)
  13. コンマ(,)を入力
  14. 検索対象のセル範囲を選択
    ※今回はA1~C1セル(絶対参照)
  15. コンマ(,)を入力
  16. 転記したいデータのシートへ移動
    ※今回は「商品マスタ」シート
  17. 0」を選択 or 入力
  18. ),」を入力
  19. FALSE」を選択 or 0」を入力
    ※今回は「0
  20. Enter」キーで確定
  21. ベースの数式をコピーし、以降のセルへペースト
    ※今回はD2~E11セルへペースト

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

VLOOKUP+MATCHの組み合わせテクニックは、別表からの複数列のデータ転記に役立ちます。

実務での使用頻度も高く、Excelのバージョンに影響なく使えるため、この機会に覚えておくことを強くおすすめします!

なお、VLOOKUP+MATCH以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。



ご参考になれば幸いですm(_ _)m

森田森田

VLOOKUP+MATCHの組み合わせができれば、他のINDEX+MATCHOFFSET+MATCHといった別関数の組み合わせも応用的に対応できます。
なので、最初の方はややこしいと思いますが、ぜひ覚えてほしいテクニックの1つですね。

いまいち理解しにくい場合は、VLOOKUPMATCHを別の数式にし、MATCHの戻り値をVLOOKUPの引数「列番号」の参照セルにするところから段階的に始めることをおすすめします!