【Excel関数】VLOOKUP関数を右方向にドラッグ＆ドロップしてもずれないようにする

こんにちは、ふみです。みなさんは Microsoft Excel でVLOOKUP関数を入力したセルを右方向にドラッグ＆ドロップでコピーした後の、範囲と列番号の修正を面倒に感じたことはありませんか？

今回はVLOOKUP関数を入力したセルを横方向にドラッグ＆ドロップでコピーしても数式の修正が不要になるやり方を紹介します。

CONTENTS

1 やり方
2 解説
3 まとめ

やり方

ここではデータベースからエクスポートしたExcelデータを、別のシートからVLOOKUP関数で検索値の情報を抽出する場合を解説します。

VLOOKUP関数の数式を横方向にドラッグ＆ドロップでコピーしても数式の修正が不要になるやり方は次のとおりです。

１．
VLOOKUP関数を設定するシートに参照先のデータと同じ順番でインデックスを設定する。

VLOOKUP関数を設定するシートに参照先のデータと同じ順番でインデックスを設定した状態

２．
1行目にインデックス、A列に検索値を設定した場合、セルB2に次の数式を設定する。

=VLOOKUP($A2,（参照先のシート名）!$A:B,COLUMN()-COLUMN($A:$A)+1,FALSE)

３．
右方向へドラッグ＆ドロップで右端のインデックスまで数式をコピーした後、下へドラッグ＆ドロップでコピーする。

これで完成です。数式を再設定することなくコピーできました。
上の図ではシート「馬」を参照してA列の検索値の情報を抽出しています。

このやり方は同じ構成のExcelファイルであれば、参照先のシート名を変えるだけで使いまわしができるので、完成したら見本用のExcelファイルとして自分でわかりやすい場所に保存しておくと良いです。

解説

ここでは今回使用したVLOOKUP関数の設定について解説します。

検索値

$A2

列番号Aに絶対参照の「$」を設定します。
これを設定しないと横方向にコピーしたときに参照先がずれてしまいます。
反対に行番号2の前に「$」を設定しまうと参照先がずれない為、下方向にコピーしても参照先が2行目に固定されてしまうので、相対参照の（「$」を付けない）設定にしています。

範囲

（参照先のシート名）!$A:B

列番号で範囲を指定する場合、左側の列番号Aにのみ絶対参照の「$」を設定します。
これを設定しないと横方向にコピーしたときに検索値の参照先（1列目の列番号）がずれてしまいます。
反対に右側の列番号Bに「$」を設定しまうと参照先がずれない為、検索範囲が拡張されずにエラーが表示されてしまうので、相対参照の（「$」を付けない）設定にしています。

列番号

COLUMN()-COLUMN($A:$A)+1

列番号を整数で設定した場合、参照先が固定されてドラッグアンドドロップに連動しないので、COLUMN関数を使います。
「COLUMN()」は自身のセルの列番号を算出します。
単純に「COLUMN()」のみの設定でも右方向にドラッグアンドドロップでコピーできるようになりますが、VLOOKUP関数を設定したセルの左側に列を挿入したときに列番号が変動して数式の参照がずれてしまいます。（列挿入によるレイアウト変更は多人数でExcelを使用している場合に良く発生します。）
自身のセルの列番号の「COLUMN()」から検索値の列番号「COLUMN($A:$A)」を引いた後に1を加算することで、検索値の列番号を1としたときの自身のセルの列番号を算出しています。
列番号をこの設定にすると、数式の左側に列を追加で挿入されても数式がずれないようになります。