複数列の表から検索値に一致するデータを、2列目、5列目、7列目…などの離れた複数の列を抽出するときは、XLOOKUP／VLOOKUP関数を使って数式を作成し、その数式をコピーするだけで検索抽出できる。
複数列・行から検索抽出
例題１｜検索値に該当するデータのうち離れた複数列を抽出する方法
- 求めるセル（B3セル）を選択し、「=XLOOKUP(」と入力する。
- ［検索値］…「A002」のセル（A3セル）を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
- ［検索範囲］…検索する「商品ID」のセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
- ［戻り範囲］…XLOOKUP関数で、抽出先の列見出しと同じ位置にある表のデータを抽出する数式を入力して、「Enter」キーで数式を確定する。
- ［見つからない場合］…「””」（空白）を入力する。
- ［一致モード］［検索モード］…省略して、「Enter」キーで数式を確定する。
- 数式を必要な分だけ複写する。
【数式】=XLOOKUP($A$3,$A$6:$A$9,XLOOKUP(B2,$B$5:$D$5,$B$6:$D$9),””)
Microsoft365 ： XLOOKUP関数
数式の解説
「XLOOKUP(B2,$B$5:$D$5,$B$6:$D$9)」の数式は、B2セルの「単価」が表の列見出しと一致する列にあるデータを抽出する。このデータをXLOOKUP関数の引数［戻り範囲］に組み合わせて数式を作成することで、A3セルの「A002」と一致した行にあるB2セルの「単価」が抽出される。数式をコピーするだけで、それぞれの抽出したい列見出しと同じ位置にあるデータが抽出できる。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx
例題２｜検索値に該当するデータのうち離れた複数列を抽出する方法
- 求めるセル（B3セル）を選択し、「=VLOOKUP(」と入力する。
- ［検索値］…「A002」のセル（A3セル）を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
- ［範囲］…抽出する表のセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
- ［列番号］…MATCH関数で抽出する1つ目の列見出しが表の列見出しの何番目にあるかを求める数式を入力する。
- ［検索方法］…完全一致なので「0」と入力して、「Enter」キーで数式を確定する。
数式を必要な分だけ複写する。
【数式】=VLOOKUP($A$3,$A$6:$D$9,MATCH(B2,$A$5:$D$5,0),0)
数式の解説
「MATCH(B2,$A$5:$D$5,0)」の数式は、B2セルの「単価」が表の列の見出しの何番目にあるかを求める。この番目をVLOOKUP関数の引数［列番号］に組み合わせて数式を作成することで、A3セルの「A002」と一致した行にあるB2セルの「単価」が抽出される。数式をコピーするだけで、それぞれの抽出したい列見出しが表の列見出しの何番目にあるかが求められる。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx
応用１｜
VLOOKUP関数を使って検索抽出する場合、2列目と4列目から抽出するなど、少ない列なら、❶引数［検索値］はセルの列番号の前に「$」記号を付けて列番号を固定し、❷［範囲］は絶対参照にして数式を作成しておけば、❸数式をコピーして、 ❹抽出する列番号を変更するだけで済む。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-application1.xlsx
応用２｜
Microsoft365なら、CHOOSECOLS関数の引数［行番号］にXMATCH関数を組み合わせた数式を使えば、数式のコピーなしで数式の確定と同時に抽出できる。この場合、引数［配列］にはXLOOKUP関数を組み合わせて数式を作成する必要がある。
❶求めるセル（B3セル）を選択し、「=CHOOSECOLS(」と入力する。
❷［配列］…XLOOKUP関数で、検索値と同じ行にあるすべてのデータを抽出する数式を入力する。
❸ ［列番号1］…XMATCH関数で、抽出する列見出しが表の何番目にあるかを求める数式を入力して、「Enter」キーで数式を確定する。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-application2.xlsx
コメント