検索値に電話番号を入力しても、名前を入力しても、該当するデータを表から抽出したい。そんな時は、検索値があるデータの列を抽出してから、検索値と一致する行のデータを抽出する数式を作成して求めてみましょう。
目的
指定の検索/抽出範囲から抽出
目次
例題1|抽出範囲から抽出する
- 求めるセル(A6セル)を選択し、「=FILTER(」と入力する。
- [配列]…抽出する表のセル範囲を選択する。
- [含む]…XLOOKUP関数でA2セルの検索する列見出しがある列から抽出したデータがA3セルの検索値である場合の条件式を入力する。
- [空の場合]…[””](空白)を入力して、「Enter」キーで数式を確定する。
【数式】=FILTER(A9:D12,XLOOKUP(A2,A8:D8,A9:D12)=A3,””)
数式の解説
「=FILTER(A9:D12,XLOOKUP(A2,A8:D8,A9:D12)=A3,“”)」の数式は、検索するA2セルの「種類」と一致した列にあるデータがA3セルの「レモングラス」である場合の条件を満たす、A9セル~D12セルのデータを抽出する。結果、常にA2セルの列見出しのA3セルの値に該当するデータが抽出される。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx
例題2|抽出範囲から抽出する
- 求めるセル(A6セル)を選択し、「=INDEX(」と入力する。
- [参照]…抽出するセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
- [行番号]…SUMPRODUCT関数とROW関数でA3セルの検索値が表の何行目にあるかを求める数式を入力する。
- [列番号]…「COLUMN(A2)」と入力して、「Enter」キーで数式を確定する。
- 数式を必要な分だけ複写する。
【数式】=INDEX($A$9:$D$12,SUMPRODUCT(($A$9:$D$12=$A$3)*ROW($A$1:$A$4)),COLUMN(A2))
数式の解説
「SUMPRODUCT(($A$9:$D$12=$A$3)*ROW($A$1:$A$4))」の数式は、表のデータがA3セルの「レモングラス」である場合に表内の行の番目が求められる。INDEX関数の引数[行番号]に組み合わせて数式を作成することで、種類「レモングラス」1列目の商品IDが抽出される。数式をコピーすることで、抽出する列番号が「2」「3」…と指定されるので、種類「レモングラス」に該当するデータが抽出される。結果、常にA3セルの値に該当するデータが抽出される。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx
コメント