どの列を検索対象にしても行を抽出する方法｜FILTER・XLOOKUP

検索値に電話番号を入力しても、名前を入力しても、該当するデータを表から抽出したい。そんな時は、検索値があるデータの列を抽出してから、検索値と一致する行のデータを抽出する数式を作成して求めてみましょう。

例題１｜抽出範囲から抽出する

1. 求めるセル（A6セル）を選択し、「=FILTER(」と入力する。
2. ［配列］…抽出する表のセル範囲を選択する。
3. ［含む］…XLOOKUP関数でA2セルの検索する列見出しがある列から抽出したデータがA3セルの検索値である場合の条件式を入力する。
4. ［空の場合］…［””］（空白）を入力して、「Enter」キーで数式を確定する。

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

数式の解説

「=FILTER(A9:D12,XLOOKUP(A2,A8:D8,A9:D12)=A3,“”)」の数式は、検索するA2セルの「種類」と一致した列にあるデータがA3セルの「レモングラス」である場合の条件を満たす、A9セル～D12セルのデータを抽出する。結果、常にA2セルの列見出しのA3セルの値に該当するデータが抽出される。

Excelデータダウンロード

練習用Excelファイル（無料ダウンロード）

「数式の解説」の解説で使うExcelデータです。数式と練習データが入っているので、本文の式を同じ表で試せます。

無料Excelサンプルファイルダウンロード（.xlsx・15KB）

Excelファイル（.xlsx）／登録不要／数式・練習データ入り

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

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

例題２｜抽出範囲から抽出する

1. 求めるセル（A6セル）を選択し、「=INDEX(」と入力する。
2. ［参照］…抽出するセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
3. ［行番号］…SUMPRODUCT関数とROW関数でA3セルの検索値が表の何行目にあるかを求める数式を入力する。
4. ［列番号］…「COLUMN(A2)」と入力して、「Enter」キーで数式を確定する。
5. 数式を必要な分だけ複写する。

数式の解説

「SUMPRODUCT(($A$9:$D$12=$A$3)*ROW($A$1:$A$4))」の数式は、表のデータがA3セルの「レモングラス」である場合に表内の行の番目が求められる。INDEX関数の引数［行番号］に組み合わせて数式を作成することで、種類「レモングラス」1列目の商品IDが抽出される。数式をコピーすることで、抽出する列番号が「2」「3」…と指定されるので、種類「レモングラス」に該当するデータが抽出される。結果、常にA3セルの値に該当するデータが抽出される。

Excelデータダウンロード

練習用Excelファイル（無料ダウンロード）

「数式の解説」の解説で使うExcelデータです。数式と練習データが入っているので、本文の式を同じ表で試せます。

無料Excelサンプルファイルダウンロード（.xlsx・15KB）

Excelファイル（.xlsx）／登録不要／数式・練習データ入り

FILTER 関数の基本と、目的別の式の早見表はFILTER関数の使い方にまとめています。