XLOOKUPで離れた複数列をまとめて抽出｜VLOOKUP・MATCHとの比較

複数列の表から検索値に一致するデータを、2列目、5列目、7列目…などの離れた複数の列を抽出するときは、XLOOKUP／VLOOKUP関数を使って数式を作成し、その数式をコピーするだけで検索抽出できる。

XLOOKUP の基本（書き方・一致モード・下から検索・VLOOKUP との違い）は、XLOOKUP関数の使い方にまとめています。

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

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

例題１｜検索値に該当するデータのうち離れた複数列を抽出する方法

1. 求めるセル（B3セル）を選択し、「=XLOOKUP(」と入力する。
2. ［検索値］…「A002」のセル（A3セル）を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
3. ［検索範囲］…検索する「商品ID」のセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
4. ［戻り範囲］…XLOOKUP関数で、抽出先の列見出しと同じ位置にある表のデータを抽出する数式を入力して、「Enter」キーで数式を確定する。
5. ［見つからない場合］…「””」（空白）を入力する。
6. ［一致モード］［検索モード］…省略して、「Enter」キーで数式を確定する。
7. 数式を必要な分だけ複写する。

▶ この式（B3）を、上の練習ツールで試す ／ この式（C3）を、上の練習ツールで試す

数式の解説

「XLOOKUP(B2,$B$5:$D$5,$B$6:$D$9)」の数式は、B2セルの「単価」が表の列見出しと一致する列にあるデータを抽出する。このデータをXLOOKUP関数の引数［戻り範囲］に組み合わせて数式を作成することで、A3セルの「A002」と一致した行にあるB2セルの「単価」が抽出される。数式をコピーするだけで、それぞれの抽出したい列見出しと同じ位置にあるデータが抽出できる。

Excelデータダウンロード

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

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

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

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

例題２｜検索値に該当するデータのうち離れた複数列を抽出する方法

1. 求めるセル（B3セル）を選択し、「=VLOOKUP(」と入力する。
2. ［検索値］…「A002」のセル（A3セル）を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
3. ［範囲］…抽出する表のセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
4. ［列番号］…MATCH関数で抽出する1つ目の列見出しが表の列見出しの何番目にあるかを求める数式を入力する。
5. ［検索方法］…完全一致なので「0」と入力して、「Enter」キーで数式を確定する。
   数式を必要な分だけ複写する。

数式の解説

「MATCH(B2,$A$5:$D$5,0)」の数式は、B2セルの「単価」が表の列の見出しの何番目にあるかを求める。この番目をVLOOKUP関数の引数［列番号］に組み合わせて数式を作成することで、A3セルの「A002」と一致した行にあるB2セルの「単価」が抽出される。数式をコピーするだけで、それぞれの抽出したい列見出しが表の列見出しの何番目にあるかが求められる。

Excelデータダウンロード

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

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

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

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

応用１｜

VLOOKUP関数を使って検索抽出する場合、2列目と4列目から抽出するなど、少ない列なら、❶引数［検索値］はセルの列番号の前に「$」記号を付けて列番号を固定し、❷［範囲］は絶対参照にして数式を作成しておけば、❸数式をコピーして、 ❹抽出する列番号を変更するだけで済む。

Excelデータダウンロード

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

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

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

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

応用２｜

Microsoft365なら、CHOOSECOLS関数の引数［行番号］にXMATCH関数を組み合わせた数式を使えば、数式のコピーなしで数式の確定と同時に抽出できる。この場合、引数［配列］にはXLOOKUP関数を組み合わせて数式を作成する必要がある。
❶求めるセル（B3セル）を選択し、「=CHOOSECOLS(」と入力する。
❷［配列］…XLOOKUP関数で、検索値と同じ行にあるすべてのデータを抽出する数式を入力する。
❸ ［列番号1］…XMATCH関数で、抽出する列見出しが表の何番目にあるかを求める数式を入力して、「Enter」キーで数式を確定する。

Excelデータダウンロード

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

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

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

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