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

2020年5月13日

VLOOKUP関数は検索結果として1つの列(セル)を返しますが

2列以上(セル)を返したい場合、列番号を変えながら複数セルに

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

出来れば数式を複製したいですが、普通にコピー&ペーストしてしまうと

列番号はカウントアップせず最初の数式のままの数値でコピーされてしまいます。

下の例のように同じ検索結果の「サバ」が全てのセルに表示されます。

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

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

列が多いと手間と負担が大きく、間違える可能性も高くなります。

そのためこの記事では単純なコピー&ペーストで済むように

(1列ずつ参照がズレるように)数式を組む方法を紹介します。

手順

手順は5つあります。

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

1つめのXLOOKUP関数VLOOKUP関数の多くの問題を解決した新関数です。

こちらを使用できるバージョンであれば変更することが最適解です。

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

3つめの方法はVLOOKUP関数のセルのみで完結させたい場合に使用しましょう。

4つ目はVLOOKUP関数ではなくINDEX関数とMATCH関数を組み合わせる方法です。

数式が若干読みにくいですが、VLOOKUP関数よりスマートです。

知名度の低い関数である欠点が気にならない場合はこれが良いです。

XLOOKUP関数の方が上位互換ですが、古いバージョンでも使える利点があります。

XLOOKUP関数を使用

XLOOKUP関数では表示列が番号指定だったものが範囲指定になり

コピー&ペーストオートフィルにも反応してくれます。

今回の例では以下のようにJ4セルに設定し、K~Lにコピー&ペーストすれば完成です。

=XLOOKUP($I4,$C4:$C9,D4:D9)
XLOOKUP関数での実現例

Office365やExcel2019以降であれば列方向のスピル形式で指定する方法も効率的です。

それにはJ4セルに以下のように設定します。

=XLOOKUP(I4,C4:C9,D4:F9)

この数式を入力するとコピー&ペーストなしで自動的にK~Lに数式が拡大されます。

XLOOKUP関数(スピル)での実現例

この他にも多くの点で改善されているため

利用できるバージョンであればXLOOKUP関数を使用しましょう。

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

任意の行(下のサンプルでは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関数を組み合わせる方法の図解

これにより効率的に検索結果を複数列、抽出することが可能です。

関連記事

「VLOOKUP関数 複数」についてのガイド

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