VLOOKUP関数をオートフィルでコピー

たくさんのデータをVLOOKUP 関数で表引きする時に、数式をオートフィルでコピーした場合の面倒を解決する技を紹介しています。

2013Excel関数技36回

VLOOKUP関数をオートフィルでコピー

エクセルのVLOOKUP関数は、とても便利でいろいろな場面で使われています。
でも数式をコピーするときに、気をつけないといろいろと面倒なことが起こります。
いつも数式のコピーの後で引数の修正をしているなら、こんな方法で解決です。 そんな、面倒なことにならないスマートなエクセルのVLOOKUP関数の使い方を紹介します。
(サンプルファイルは、こちらから 関数技36回サンプルデータ

VLOOKUP関数の数式をコピー

データベース形式の表からVLOOKUP関数を使ってデータを表示するようなケースはよくありますね。 データベース形式の表は、テーブルとして書式設定してあり、名前は”USERS”としてあります。
VLOOKUP関数の使い勝手を良くする1
このシートに調べたいIDあるいはコードなどを入力して、該当するデータをすべて表示しようと思います。 データは、VLOOKUP 関数で簡単に表引きできます。
VLOOKUP関数の使い勝手を良くする2
図のようにB2セルにVLOOKUP関数の数式を入力すれば、通常ならそのままコピーできますが、
VLOOKUP関数の使い勝手を良くする3
VLOOKUP 関数の引数である、列指定の部分、これは自動で変化しません。 コピーした結果は、コピー元の繰り返しになってしまいます。
VLOOKUP関数の使い勝手を良くする4
D列ならば、VLOOKUP 関数の引数である、列指定の部分を4に変更する必要があります。
VLOOKUP関数の使い勝手を良くする5

列数を取得する関数COLUMNS関数

そこで、自動的に該当する列番号を取得する関数としてCOLUMNS関数を使いましょう。
たとえば、B2セルに=COLUMNS($A$3:B3)と入力すれば、返り値として2が表示されます。
VLOOKUP関数の使い勝手を良くする6
この式をVLOOKUP関数にネストすれば、自動的に列数を取得できます。
=VLOOKUP($A$3,USERS,COLUMNS($A$3:B3),0)
VLOOKUP関数の使い勝手を良くする6
たくさんのデータをVLOOKUP 関数で表引きする時に、列指定にCOLUMNS関数を利用すると、 オートフィルでコピーした場合に修正の必要がなくなります。
VLOOKUP関数の使い勝手を良くする6
これにより時短になり、ミスも起きにくくなります。