s035｜複数ある検索値の全てに該当するデータを抽出する方法【XLOOKUP関数、VLOOKUP関数】｜Excel関数によるデータ抽出方法

XLOOKUP／VLOOKUP関数は1つの検索値でしか抽出できないが、複数ある検索値でも1つの文字列にしてしまえば抽出できる。名前は同じでも電話番号が違う同姓同名から該当するデータを抽出したい場合などに活用しよう。

例題１｜複数条件検索で抽出する（XLOOKUP）

1. ［電話番号］を求めるセル（B4セル）を選択し、「=XLOOKUP(」と入力する。
2. ［検索値］…「氏名」のセル&「生年月日」のセルで入力する。
3. ［検索範囲］…「氏名」のセル範囲&「生年月日」のセル範囲で入力する。
4. ［戻り範囲］…抽出する「電話番号」のセル範囲を選択する。
5. ［見つからない場合］…［””］（空白）を入力する。
6. ［一致モード］［検索モード］…省略して、「Enter」キーで数式を確定する。

【数式】 =XLOOKUP(B2&B3,B7:B10&C7:C10,D7:D10,””)

数式の解説

「=XLOOKUP(B2&B3,B7:B10&C7:C10,D7:D10,“”)」の数式は、「木村洋子1990/9/10」を表の「氏名」のセル範囲&「生年月日」のセル範囲から検索し、一致した行にあるD7セル～D10セルの「電話番号」を抽出することが出来る。結果、「木村洋子」と「1990/9/10」の2つのキーで「電話番号」が抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx  

例題２｜複数条件検索で抽出する（VLOOKUP）

1. A列のセルを選択し、「氏名」のセルと［生年月日］のセルを「&」で結合する数式を入力する。
2. 数式を必要な分だけ複写する。
3. ［電話番号］を抽出セル（C4セル）を選択し、「=VLOOKUP(」と入力する。
4. ［検索値］…検索値の「氏名」と「生年月日」を「氏名」のセル&「生年月日」のセルで入力する。
5. ［範囲］…抽出する表のセル範囲を選択する。
6. ［列番号］…抽出する列の番目「5」を入力する。
7. ［検索方法］…完全一致なので「0」と入力して、「Enter」キーで数式を確定する。

【数式】 =VLOOKUP(C2&C3,A7:E10,5,0)

数式の解説

「=C7&D7」の数式を作成して他の行にもコピーして、「氏名」&「生年月日」の列を作成しておく。「&」でつないで1つの文字列にしておけば、検索値が1つになるのでVLOOKUP関数で抽出できるようになる。この場合、VLOOKUP関数の引数の［検索値］にも複数の検索値を1つの文字列にした値を指定する必要があるので「=VLOOKUP(C2&C3,A7:E10,5,0)」と数式を作成する。結果、「木村洋子」と「1990/9/10」の2つのキーで「電話番号」が抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx  

応用１｜

2列のデータを検索値にしてクロス表に抽出する場合も、❶2列のデータを［&］で結合して1つのデータにし、❷クロス表に抽出するときは行列見出しを［&］で結合して1つの検索値にすることで抽出できる。

【数式】=XLOOKUP(A9:A10&B8:C8,A3:A6&B3:B6,C3:C6)

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-application1.xlsx