XLOOKUPで検索した行の複数列をまとめて取り出す方法｜VLOOKUP×COLUMNも解説

▶ 複数のIDをまとめて検索する式を、上の練習ツールで試す

表から検索値に一致するデータを2列～4列など連続する列で抽出したいとき、いちいち数式で抽出する列を指定せずに求めたいときは、XLOOKUP関数なら引数［戻り範囲］にすべての列のセル範囲を指定、VLOOKUP関数なら引数［列番号］にCOLUMN関数を組み合わせた数式で実現できる。

まず動かしてみよう｜この記事のサンプルをその場で試せます

練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。

例題１｜検索値に該当するデータのうち連続する列を抽出する方法

1. 求めるセル（A6セル）を選択し、「=XLOOKUP(」と入力する。
2. ［検索値］…「A002」のセル（A3セル）を選択する。
3. ［検索範囲］…検索する「商品ID」のセル範囲を選択する。
4. ［戻り範囲］…抽出する表のセル範囲を選択する。
5. ［見つからない場合］…「””」（空白）を入力する。
6. ［一致モード］［検索モード］…省略して、「Enter」キーで数式を確定する。

▶ この式（A6）を、上の練習ツールで試す

数式の解説

「=XLOOKUP(A3,A9:A12,A9:D12,“”)」の数式は、A3セルの「A002」を在庫検索の表の「商品ID」（A9セル～A12セル）から検索し、一致した行にあるA9セル～D12セルのデータを抽出する。見つからない場合は、空白を求める。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx  

例題２｜検索値に該当するデータのうち連続する列を抽出する方法

1. 求めるセル（A6セル）を選択し、「=VLOOKUP(」と入力する。
2. ［検索値］…「A002」のセルを選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
3. ［範囲］…抽出する表のセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
4. ［列番号］…抽出する列の番目「1」を「COLUMN(A1)」で入力する。
5. ［検索方法］…完全一致なので「0」と入力して、「Enter」キーで数式を確定する
6. 数式を必要な分だけ複写する。

▶ この式（A6〜D6）を、上の練習ツールで試す

数式の解説

VLOOKUP関数の引数［検索値］［範囲］に指定するセル範囲は、コピーしてもズレないように絶対参照にする必要がある。引数［列番号］にはCOLUMN関数を使用して列番号を求めると、VLOOKUP関数の引数の［列番号］に「種類」のセルには「2」、「単価」のセルには「3」、「数量」のセルには「4」が指定されるので、結果、「商品ID」に該当するすべての列のデータが抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx  

応用１｜

検索値に該当する連続行のデータを抽出する場合は、XLOOKUP関数なら、❶引数［検索値］にすべての行のセル範囲を選択するだけで、数式の確定と同時に抽出できる。

VLOOKUP関数なら、❷引数［範囲］には手順のように絶対参照にして、ほかの行に数式をコピーしよう。ただし、あらかじめ他の行に数式を入力しておきたいときは、検索値が無いので、引数だけでは対処できないVLOOKUP関数ではエラー値が求められてしまう。この場合は、❸IFNA（IFERROR）関数の引数［値］にVLOOKUP関数の数式を組み合わせて数式を作成しましょう。

複数の検索値に該当する連続列をまとめて抽出する方法はこちらです。