【方法を3つ解説】vlookupで列番号を可変にして複数取得／横にコピーしたい

vlookup関数を使って資料を作っているときに、

・関数をコピーして横に広げると、列の指定がうまくいかない…
・複数の列の値を結合して一気に抽出したい…

と思ったことありませんか？
今回はこの2つの悩みに対してお答えしていきます！

vlookupで列番号を可変して複数列を抽出する方法3つ

列番号を手動で振る

難易度：2.0
汎用性：2.0

まずは一番わかり易いものを紹介します。
こちらはわかりやすいのですが、列が途中で挿入された場合や、入れ替えられたときに対応できないと思います。
ちょっとした集計には使えるものになります。

画像内の赤枠に指定したIDの人の名前と出身地を抽出したいという例題で解説をしていきたいと思います。

通常のように抽出すると

=VLOOKUP(E3 , A2:C10 , 2 , false)
となりますね。

3つめの引数を動的に指定をしていきたいと思います。
列が2→3→…というように可変させたいので、
関数が入る上部に列を振ってしまいます。

このようにすれば、列指定を記入した列番号のセルにしていけば問題ありませんね。

あとは、相対参照を間違えないように振っていって、、、

=VLOOKUP($E3,$A$2:$C$10,F$1,false)

あとは、横、縦にコピーをしていけば一気に抽出をすることができます。

これで抽出ができました。

最終的な数式はこんな感じです。

match関数を使って列を指定する

難易度：2.5
汎用性：4.0

次は、列名によって列番号を取得して、それによって可変していく方法を紹介します。
この方法では列が挿入されたり、入れ替えが起こってもそれに応じて値が可変するため1つめの方法よりも汎用性が高くなっています。

今回はmatch関数を使っていきます。
match関数とは、指定の範囲から指定の値を検索して、それが何番目にあるのか？を教えてくれる関数です。
ここでわかった方もいるかもしれませんが、このmatch関数で探したい列名を探して、それが何列目なのかを返してもらう、という方法をやっていきます。

match関数とは？
MATCH(検査値, 検査範囲, [照合の型])
※今回は照合の型は完全一致の0を使っていきます。

まずは、列番号を出したいため、match関数のみを書いていきます。
列番号は、列がずれるごとに2→3→…と可変させたいため、
F3内の列番号を「2」と表示させるようにします。

=MATCH(F$2,$A$1:$C$1,0)

このように指定してあげることで、
F2の名前のものを
A1〜C1の中から
0=完全一致で探してください
という指示になり、「2」を得ることができます。

参照をつけて、コピーしてあげれば隣のセルでは、計算結果は「3」となります。

ここまでできたら、この関数をvlookup関数の中に入れ込んでいきましょう。

これを縦横にコピーしていくと…

抽出できましたね！
念の為関数も載せておきます。

hlookup関数を使って列を指定する

難易度：3.5
汎用性：4.0

最後にもう少し応用をして、いきたいとおもいます。
3つめの例では、

・COLUMN関数
・HLOOKUP関数

を使って列を可変していきたいと思います。
少し工程が複雑なので、結果から載せます。

工程は、
①COLUMN関数で指定したい列番号を取得
↓
②HLOOKUP関数でその番号を取得
↓
③VLOOKUP関数に入れ込む
です。

①では、
=COLUMN()
と入力するとそのセルの列番号が抽出できる特性を利用し、
データの2行目に関数を追加します。

これで
項目名が「名前」は2列目、「出身地」は3列目
ということがわかります。
後は項目名が指定されたときに3列目、4列目、と値を返してあげる関数を書く必要があります。
それがHLOOUP関数になります。
VLOOKUP関数は範囲内で○列目の値を抽出しますが、
HLOOKUP関数は範囲内で○行目の値を抽出します。

今回の場合では、
名前という値を指定されたときに「2」という値を返してほしいので、

HLOOKUP(F$3,$A$1:$C$2,2,false)

という数式を作っておきます。（上記で項目名に応じて列番号が取得されるようになる）