Excel VLOOKUP関数で複数列抽出をコピーで効率的に行う方法
VLOOKUP関数は検索結果として1つの列（セル）を返しますが
2列以上（セル）を返したい場合、列番号を変えながら複数セルに
VLOOKUP関数を作ることになります。
出来れば数式を複製したいですが、普通にコピー＆ペーストしてしまうと
列番号はカウントアップせず最初の数式のままの数値でコピーされてしまいます。
下の例のように同じ検索結果の「サバ」が全てのセルに表示されます。
手作業で列番号を全て書き換えれば済みますが、
列が多いと手間と負担が大きく、間違える可能性も高くなります。
そのためこの記事では単純なコピー＆ペーストで済むように
（1列ずつ参照がズレるように）数式を組む方法を紹介します。
手順
方法は4つあります。
- XLOOKUP関数を使用
- 列番号をセルに入力
- COLUMN関数を使用
- 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)
Office365やExcel2019以降であれば列方向のスピル形式で指定する方法も効率的です。
それにはJ4セルに以下のように設定します。
=XLOOKUP(I4,C4:C9,D4:F9)
この数式を入力するとコピー&ペーストなしで自動的にK～Lに数式が拡大されます。
この他にも多くの点で改善されているため
利用できるバージョンであればXLOOKUP関数を使用しましょう。
列番号をセルに入力する場合
任意の行（下のサンプルでは1行目）に列番号を記述します。
連番の場合はオートフィルで入力可能なため効率的です。
連続でなくても関数の中の数式を編集するよりも非常に楽で間違いがありません。
参照先が別シートであっても数式中で指定していれば問題ないため支障はありません。
COLUMN関数を使用する場合
列番号の指定を数値でなく下の数式に変更します。
=COLUMN() - 固定の調整列数
COLUMN関数は指定セルが（省略した場合は数式のセルが）
左から何列目になるかを数値で返す関数です。（もっとも左が1）
ここではJ列の列番号を2と指定するため10(J列)から固定で8を引きます。
これをコピーすると数式は変わりませんが、結果の列番号が都度、変わります。
これによりVLOOKUP関数の結果が変わっていきます。
INDEX関数とMATCH関数を使用
INDEX関数とMATCH関数を組み合わせると
列番号ではなくセル範囲指定となるため
$の付け方次第で1列ずつずらすことが可能です。
それにはVLOOKUP関数の代わりに下記の数式を使用します。
=INDEX(表示項目の範囲,MATCH(検索値,検索範囲,0))
これにより効率的に検索結果を複数列、抽出することが可能です。
Excelを効率的に習得したい方へ
当サイトの情報を電子書籍用に読み易く整理したコンテンツを
買い切り950円またはKindle Unlimited (読み放題) で提供中です。
Word-A4サイズ：1,400ページの情報量で
（実際のページ数はデバイスで変わります）
基本的な使い方、関数の解説(140種類)、
頻出テクニックと実用例(109種類)、
XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。
体系的に学びたい方は是非ご検討ください。
アップデートなどの更新事項があれば随時反映しています。
なお購入後に最新版をダウンロードするには
Amazonへの問い合わせが必要です。