Excel VLOOKUP関数で複数列抽出をコピーで効率的に行う方法

2019年9月15日

VLOOKUP関数は検索結果として

1つの列(セル)を返しますが

2列以上(セル)を返したい場合、

列番号を変えながら複数セルに

VLOOKUP関数を作ることになります。

出来れば数式を複製したいですが、

普通にコピー&ペーストしてしまうと

列番号はカウントアップせず、

最初の数式のままの数値で

コピーされてしまいます。

下の例のように同じ検索結果の

「サバ」が全てのセルに表示されます。

J4のVLOOKUP関数をK4とL4にコピー&ペーストした結果のキャプチャ。列番号の引数がそのままのため全て「サバ」と表示されている
J4のVLOOKUP関数をK4とL4にコピー&ペーストした結果

手作業で列番号を全て書き換えれば済みますが、

列が多いと手間と負担が大きく、

間違える可能性も高くなります。

そのためこの記事では

単純なコピー&ペーストで済むように

(1列ずつ参照がズレるように)

数式を組む方法を紹介します。

手順

手順は二つあります。

  1. 列番号をセルに入力
  2. COLUMN関数を使用
  3. INDEX関数とMATCH関数を使用

1つめの方法がシンプルのため推奨します。

2つめの方法はVLOOKUP関数のセルのみで

完結させたい場合に使用しましょう。

3つ目はVLOOKUP関数ではなく

INDEX関数とMATCH関数を

組み合わせる方法です。

数式が若干読みにくいですが、

VLOOKUPよりスマートです。

知名度の低い関数である欠点が

気にならない場合はこれが最適解です。

①列番号をセルに入力する場合

任意の行(下のサンプルでは1行目)に

列番号を記述します。

連番の場合はオートフィルで

入力可能なため効率的です。

連続でなくても関数の中の

数式を編集するよりも

非常に楽で間違いがありません。

参照先が別シートであっても

数式中で指定していれば

問題ないため支障はありません。

J1~L4セルに列番号を入力し、VLOOKUP関数で参照している状態のキャプチャ

②COLUMN関数を使用する場合

列番号の指定を数値でなく

下記の数式に変更します。

COLUMN() - 固定の調整列数

COLUMN関数は指定セルが

(省略した場合は数式のセルが)

左から何列目になるかを

数値で返す関数です。(もっとも左が1)

ここではJ列の列番号を2と指定するため

10(J列)から固定で8を引きます。

これをコピーすると数式は変わりませんが、

結果の列番号が都度、変わります。

これによりVLOOKUP関数の

結果が変わっていきます。

COLUMN関数を使用しVLOOKUP関数をコピーしたキャプチャ
列番号にCOLUMN関数を使用しVLOOKUP関数をコピーした結果

③INDEX関数とMATCH関数を使用

INDEX関数とMATCH関数を組み合わせると

列番号ではなくセル範囲指定となるため

$の付け方次第で1列づつズラすことが可能です。

それにはVLOOKUP関数の代わりに

下記の数式を使用します。

INDEX(表示項目の範囲,MATCH(検索値,検索範囲,0))
INDEX関数とMATCH関数を組み合わせる方法の図解

これにより効率的に検索結果を

複数列、抽出することが可能です。

この方法は他にも利点があるため

興味がある方は下の記事の参照をお願いします。

新関数XLOOKUP関数の場合

なお2019年後半にXLOOKUP関数という

かなり便利な上位版がリリース予定です。

こちらは列番号でなく

セル範囲指定になるため

絶対参照と使い分けることで

解決するようになると思われます。

関連記事

息抜きわんこ動画

VLOOKUP関数の使い方・活用方法の記事一覧

セルの列と行の位置を取得(ROW関数とCOLUMN関数)

フォローする