s023｜検索値に該当するデータのうち連続する列を抽出する方法【XLOOKUP関数、VLOOKUP関数、COLUMN関数】｜Excel関数によるデータ抽出方法

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

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

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

【数式】=XLOOKUP(A3,A9:A12,A9:D12,””)

数式の解説

「=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. 数式を必要な分だけ複写する。

【数式】=VLOOKUP($A$3,$A$9:$D$12,COLUMN(A1),0)

数式の解説

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

Excelデータダウンロード

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

応用１｜

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

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