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完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。