s034｜検索値を含むワードを検索して該当データを抽出する方法【XLOOKUP関数、LOOKUP関数】｜Excel関数によるデータ抽出方法

検索値の一部しかない検索対象のデータから、該当データを抽出するなら、XLOOKUP／LOOKUP関数の引数［検索範囲］にFIND関数を使って数式を作成しましょう。

例題１｜部分一致検索で抽出する（XLOOKUP）

1. ［問い合わせ先］を求めるセル（B3セル）を選択し、「=XLOOKUP(」と入力する。
2. ［検索値］…「0」と入力する。
3. ［検索範囲］…検索する「管轄」のセル範囲を「0*FIND(A6:A8,B2)」で入力する。
4. ［戻り範囲］…抽出する「問い合わせ先」のセル範囲を選択する。
5. ［見つからない場合］…［””］（空白）を入力する。
6. ［一致モード］［検索モード］…省略して、「Enter」キーで数式を確定する。

【数式】=XLOOKUP(0,0*FIND(A6:A8,B2),B6:B8,””)

数式の解説

FIND関数は、位置が見つからないとエラー値「#VALUE!」を求める。そのため、「FIND(A6:A8,B2)」の数式は、1行目の「A市」だけ位置の「4」を求め、その他はエラー値を求める。これを「0」で乗算すると「4」だけは「0」が求められる。この値をXLOOKUP関数の［検索値］に組み合わせて「=XLOOKUP(0,0*FIND(A6:A8,B2),B6:B8,“”)」の数式を作成すると、「0」に対応する「問い合わせ先」が抽出される。結果、検索「千葉県A市」に該当する問い合わせ先「080-****-0011」が抽出される。

Excelデータダウンロード

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

例題２｜部分一致検索で抽出する（LOOKUP）

1. ［問い合わせ先］を抽出するセル（B3セル）を選択し、「=LOOKUP(」と入力する。
2. ［検査値］…「0」と入力する。
3. ［検査範囲］…検索する「管轄」のセル範囲を「0*FIND(A6:A8,B2)」で入力する。
4. ［対応範囲］…抽出する「問い合わせ先」のセル範囲を選択したら、「Enter」キーで数式を確定する。

【数式】=LOOKUP(0,0*FIND(A6:A8,B2),B6:B8)

数式の解説

LOOKUP関数の［検査値］に「0」を指定して、［検査範囲］にXLOOKUP関数と同じように「FIND(A6:A8,B2)」を指定して数式を作成すると、「0」に対応する「問い合わせ先」が抽出される。結果、検索「千葉県A市」に該当する問い合わせ先「080-****-0011」が抽出される。

Excelデータダウンロード

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