vlookup関数について

質問

ある表をベースにして別のファイルにvlookupで反映させる時、たとえばＢ列からＦ列までに関数を入れます。このときに、ドラッグしてコピーをするのですが、反映させる列番目の数字は一列ずつ入力して変えなければならないのでしょうか？
簡単な技があれば教えて下さい。
抽象的な質問で申し訳ありません。

回答No.3

keithin · ベストアンサー

たとえば
Ｂ２に
VLOOKUP($A2,Sheet2!$A:$F,2,FALSE)
のように「＝を付けないで」記入します

Ｂ２を右に引っ張って２のところを３，４…に自動でやらせます
Ｃｔｒｌ＋Ｈで置換を出して
VLOOKUP
を
=VLOOKUP
にすべて置換してやれば、欲しかった数式その通りが出来ます。

下向けにはふつーにオートフィルドラッグします。

お礼

とてもシンプルで役立ちました。ありがとうございます。

回答No.2

kagakusuki

例えばそのVLOOKUP関数が入力されている中で、一番右端の列であるB列の２行目のセルに入力されている関数が、

=VLOOKUP($A2,Sheet1!$C$2:$Z$99,3,FALSE)

という様なものであったとします。
　この関数は、Sheet1のC2:C99の範囲内において、A2セルに入力されているのと同じ値が入力されている行を見つけ出して、その行の「Sheet1!C列から数えて3列目の列であるSheet1!E列」のセルに入力されている値を返す関数という事になります。
　その際、列番号として指定されている３という数は、Sheet1のC列～E列の範囲が何列分の列に相当するのかという事を指定している値なのですから、この３と記述されている箇所を

=VLOOKUP($A2,Sheet1!$C$2:$Z$99,COLUMNS(Sheet1!$C$2:$E$99),FALSE)

という具合に

COLUMNS(Sheet1!$C$2:$E$99)

という関数で置き換える事が出来ます。
　更に、この関数をコピーした際に、列番目の数字をを可変にするのですから、E列の所を指定している箇所を相対参照の形式に変更して、

=VLOOKUP($A2,Sheet1!$C$2:$Z$99,COLUMNS(Sheet1!$C$2:E$99),FALSE)

としますと、これをC列にコピーした際には、

=VLOOKUP($A2,Sheet1!$C$2:$Z$99,COLUMNS(Sheet1!$C$2:F$99),FALSE)

という形になり、

COLUMNS(Sheet1!$C$2:F$99)

が返す値は4なのですから、C2:C99の範囲内において、A2セルに入力されているのと同じ値が入力されている行を見つけ出して、その行の「C列から数えて4列目の列である"F列"」のセルに入力されている値を返す関数に、自動的に変わる様にする事が出来ます。

　そして、COLUMNS関数では、その中において参照しているセル範囲の行番号が何行目の行であっても、返す値に影響する事はありませんから、COLUMNS関数の中でセル範囲を指定する際に、Sheet1!$C$2:E$99の様に行番号を指定する必要は無く、

COLUMNS(Sheet1!$C:E)

とするだけで、3を指定する事が出来る訳です。
　これをVLOOKUP関数に使いますと

=VLOOKUP($A2,Sheet1!$C$2:$Z$99,COLUMNS(Sheet1!$C:E),FALSE)

となります。

　ついでに申し上げますと、VLOOKUP関数の２番目の項目として指定されている

Sheet1!$C$2:$Z$99

というセル範囲に関してですが、反映させる列番目の数字が3の場合には、必要となるセル範囲は

Sheet1!$C$2:$E$99

の範囲だけであり、

Sheet1!$D$2:$Z$99

の範囲まで指定する必要は無い訳ですから、この部分も後ろの方の列の参照形式を相対参照として

=VLOOKUP($A2,Sheet1!$C$2:E$99,COLUMNS(Sheet1!$C:E),FALSE)

としますと、右方向にドラッグしてコピーする毎に、

B2セル　　=VLOOKUP($A2,Sheet1!$C$2:E$99,COLUMNS(Sheet1!$C:E),FALSE)
C2セル　　=VLOOKUP($A2,Sheet1!$C$2:F$99,COLUMNS(Sheet1!$C:F),FALSE)
D2セル　　=VLOOKUP($A2,Sheet1!$C$2:G$99,COLUMNS(Sheet1!$C:G),FALSE)
E2セル　　=VLOOKUP($A2,Sheet1!$C$2:H$99,COLUMNS(Sheet1!$C:H),FALSE)

という具合に、コピーする列に合わせて、参照先の列範囲を増やす事が出来ます。

お礼

COLUMNSという関数を初めて知ったので、関数の知識を増やしたい私にとってはとてもありがたいものでした。しかもこれほど詳しくかつ分かりやすく説明してくださりありがとうございます。

回答No.1

noname#203218

仮に検索データ範囲がＨ３～Ｍ８とします。
検索文字列はＡ列、検索結果はB列～Ｆ列とします。

Ｂ３セル数式=VLOOKUP($A3,$H$3:$M$8,COLUMN(B2),0)
数式を指定範囲でコピペで可能です。

セルの相対参照、絶対参照はご存知だと思いますので割愛します。
関数COLUMNは列番号を返します。COLUMN(B2)の場合は２を返します。式をコピーするとＣ３式は=VLOOKUP($A3,$H$3:$M$8,COLUMN(Ｃ2),0)と変化する事で検索値が得られます。

下記図参照下さい。
図のような結果を得たいのであれば良いのですが。

お礼

画像付きでわかりやすく説明してくださりありがとうございました。
色々と勉強になりました。