MENU

CHOOSEROWSとXMATCHで複数の検索値の行をまとめて抽出する

CHOOSEROWSとXMATCHで複数の検索値の行をまとめて抽出する

複数列の表から、検索値に一致する行ごとに2列~4列など連続する列で抽出したいときは、CHOOSEROWS関数の引数[行番号]にXMATCH関数を組み合わせて数式を作成することで、数式確定と同時に一発で抽出できる。

目的

複数列・行から検索抽出

使用する関数
目次

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

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

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

手順:B3 に式を入れて Enter

商品ID(A3:A4)に当たる行の種類と単価を、下の表からまとめて取り出します。B3 に次の式を入力して、Enter キーを押します❶。

B3 に入れる式
=CHOOSEROWS(B7:C10,XMATCH(A3:A4,A7:A10))
B7:C10 配列(種類と単価)XMATCH(A3:A4,A7:A10) 行番号(A3:A4 が何行目か)
ひとことで商品ID(A002・A003)が何行目かを調べ、その行の種類と単価をまとめて取り出す
式を分けて読むと(色は画像の枠と同じ)
配列B7:C10
どこから取り出す?
→ 種類と単価の列
行番号XMATCH(A3:A4,A7:A10)
何行目?
→ A002 は 2 行目、A003 は 3 行目
自分の表で使うときは
XMATCHに「探す ID の範囲」をまとめて渡すと、行番号も複数になり、その行が全部取り出されます。例:E2:E5 の商品コードの、名前と価格=CHOOSEROWS(B2:C100,XMATCH(E2:E5,A2:A100))
  • Microsoft 365・Excel 2024 で使えます(Excel 2021 以前では使えません)。結果が広がる先のセルに何か入っていると #SPILL! になります。
エクセルのCHOOSEROWS関数とXMATCH関数で複数の商品IDの行をまとめて取り出す式を色分けし、結果が広がる範囲を示した画面
エクセルのCHOOSEROWS関数とXMATCH関数で複数の商品IDの行をまとめて取り出す式を色分けし、結果が広がる範囲を示した画面

確認:A002 と A003 の 2 行がまとめて出た

エクセルのCHOOSEROWS関数とXMATCH関数で、A002とA003の行の種類と単価がB3からC4に並んだことを色で示した画面
エクセルのCHOOSEROWS関数とXMATCH関数で、A002とA003の行の種類と単価がB3からC4に並んだことを色で示した画面

種類と単価の 2 行が B3:C4 に並びました❷。式は、次の順に計算しています。

  1. XMATCHで、A002・A003 が商品ID の列(A7:A10)の何行目かを調べる
    ⇒ 2・3
  2. CHOOSEROWS で、種類と単価(B7:C10)の 2 行目と 3 行目を取り出す
    ⇒ ペパーミント 2,200 / ローズマリー 2,500

数式の解説

「XMATCH(A3:A4,A7:A10)」の数式は、検索値「商品ID」(A3セル~A4セル)が、A7セル~A10セル「商品ID」の中でそれぞれ何番目にあるかを求める。CHOOSEROWS関数の引数[行番号1]に組み合わせて数式を作成することで、検索値「商品ID」と一致した行にある「種類」「単価」が抽出される。

Excelデータダウンロード

練習用Excelファイル(無料ダウンロード)
この記事の解説で使うExcelデータです。数式と練習データが入っているので、本文の式を同じ表で試せます。
Excelファイル(.xlsx)/登録不要/数式・練習データ入り

動的配列・スピル系の関数の全体像は、こちらのまとめで整理しています。

複数のデータをまとめて取り出すときは、INDEX+XMATCHとスピルで複数データを一括抽出する方法もあわせてご覧ください。

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

この記事を書いた人

目次