表から検索値に一致するデータを2列~4列など連続する列で抽出したいとき、いちいち数式で抽出する列を指定せずに求めたいときは、XLOOKUP関数なら引数[戻り範囲]にすべての列のセル範囲を指定、VLOOKUP関数なら引数[列番号]にCOLUMN関数を組み合わせた数式で実現できる。
複数列・行から検索抽出
Excel2021/365 :XLOOKUP関数
Excel2019/2016:VLOOKUP関数、COLUMN関数
例題1|検索値に該当するデータのうち連続する列を抽出する方法
- 求めるセル(A6セル)を選択し、「=XLOOKUP(」と入力する。
- [検索値]…「A002」のセル(A3セル)を選択する。
- [検索範囲]…検索する「商品ID」のセル範囲を選択する。
- [戻り範囲]…抽出する表のセル範囲を選択する。
- [見つからない場合]…「””」(空白)を入力する。
- [一致モード][検索モード]…省略して、「Enter」キーで数式を確定する。
【数式】=XLOOKUP(A3,A9:A12,A9:D12,””)
Excel2021/365 :XLOOKUP関数
数式の解説
「=XLOOKUP(A3,A9:A12,A9:D12,“”)」の数式は、A3セルの「A002」を在庫検索の表の「商品ID」(A9セル~A12セル)から検索し、一致した行にあるA9セル~D12セルのデータを抽出する。見つからない場合は、空白を求める。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx
例題2|検索値に該当するデータのうち連続する列を抽出する方法
- 求めるセル(A6セル)を選択し、「=VLOOKUP(」と入力する。
- [検索値]…「A002」のセルを選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
- [範囲]…抽出する表のセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
- [列番号]…抽出する列の番目「1」を「COLUMN(A1)」で入力する。
- [検索方法]…完全一致なので「0」と入力して、「Enter」キーで数式を確定する
- 数式を必要な分だけ複写する。
【数式】=VLOOKUP($A$3,$A$9:$D$12,COLUMN(A1),0)
Excel2019/2016:VLOOKUP関数、COLUMN関数
数式の解説
VLOOKUP関数の引数[検索値][範囲]に指定するセル範囲は、コピーしてもズレないように絶対参照にする必要がある。引数[列番号]にはCOLUMN関数を使用して列番号を求めると、VLOOKUP関数の引数の[列番号]に「種類」のセルには「2」、「単価」のセルには「3」、「数量」のセルには「4」が指定されるので、結果、「商品ID」に該当するすべての列のデータが抽出される。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx
応用1|
検索値に該当する連続行のデータを抽出する場合は、XLOOKUP関数なら、❶引数[検索値]にすべての行のセル範囲を選択するだけで、数式の確定と同時に抽出できる。
VLOOKUP関数なら、❷引数[範囲]には手順のように絶対参照にして、ほかの行に数式をコピーしよう。ただし、あらかじめ他の行に数式を入力しておきたいときは、検索値が無いので、引数だけでは対処できないVLOOKUP関数ではエラー値が求められてしまう。この場合は、❸IFNA(IFERROR)関数の引数[値]にVLOOKUP関数の数式を組み合わせて数式を作成しましょう。
コメント