Excel|XLOOKUPの複数条件によるデータ抽出 

s023|検索値に該当するデータのうち連続する列を抽出する方法|Excelのデータ抽出方法

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

目的

複数列・行から検索抽出

使用する関数

Excel2021/365  :XLOOKUP関数

Excel2019/2016:VLOOKUP関数、COLUMN関数

目次

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

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

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

使用するExcel関数

Excel2021/365  :XLOOKUP関数

数式の解説

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

Excelデータダウンロード

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

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

  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)

使用するExcel関数

Excel2019/2016:VLOOKUP関数、COLUMN関数

数式の解説

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

Excelデータダウンロード

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

応用1|

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

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

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

コメント

コメントする

目次