複数列の表から、検索値に一致する行ごとに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 の商品コードの、名前と価格
XMATCHに「探す ID の範囲」をまとめて渡すと、行番号も複数になり、その行が全部取り出されます。例:E2:E5 の商品コードの、名前と価格
=CHOOSEROWS(B2:C100,XMATCH(E2:E5,A2:A100) ) - Microsoft 365・Excel 2024 で使えます(Excel 2021 以前では使えません)。結果が広がる先のセルに何か入っていると #SPILL! になります。

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

種類と単価の 2 行が B3:C4 に並びました❷。式は、次の順に計算しています。
- XMATCHで、A002・A003 が商品ID の列(A7:A10)の何行目かを調べる
⇒ 2・3 - 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)/登録不要/数式・練習データ入り
動的配列・スピル系の関数の全体像は、こちらのまとめで整理しています。
あわせて読みたい


Excelの動的配列・スピル関数まとめ|FILTER・SORT・UNIQUE・TEXTSPLITから応用まで
Microsoft 365のExcelで使える「動的配列(スピル)」の関数について、FILTER・SORT・UNIQUE・SORTBYといった基本から、TAKE・DROP・VSTACK・TEXTSPLITなどの整形・分割…
複数のデータをまとめて取り出すときは、INDEX+XMATCHとスピルで複数データを一括抽出する方法もあわせてご覧ください。
