Excel2021/2019/2016では、クリップボードやPower Queryで1つの表にするしかない。しかし、もしデータの変更に対応したい等の場合は、検索値に該当するデータが1つ目のシートにない場合は2つ目のシートから検索抽出する数式で対応しよう。検索抽出するシートの数だけ、XLOOKUP/VLOOKUP関数を組み合わせて数式を作成すれば良い。
目的
各シートから検索抽出
目次
例題１｜各シートから検索抽出する
- 「電話番号」を求めるセル（B3セル）を選択し、「=XLOOKUP(」と入力する。
- ［検索値］…「会員ID」のセル（A3セル）を選択する。
- ［検索範囲］…検索する「A」シートの「会員ID」のセル範囲を選択する。
- ［戻り範囲］…抽出する「A」シートの「電話番号」のセル範囲を選択する。
- ［見つからない場合］…「=XLOOKUP(」と入力する。
- ［検索値］…「会員ID」のセル範囲を選択する。
- ［検索範囲］…検索する「B」シートの「会員ID」のセル範囲を選択する。
- ［戻り範囲］…抽出する「B」シートの「電話番号」のセル範囲を選択する。
- ［見つからない場合］…［””］（空白）を入力する。
- ［一致モード］［検索モード］…省略して、「Enter」キーで数式を確定する。
【数式】=XLOOKUP(A3,A!A2:A3,A!C2:C3,XLOOKUP(A3,B!A2,B!C2,””))
数式の解説
それぞれの数式は、「A」シートのA2セル～C3セルから「B001」を検索し、見つかった場合は一致した行にある「電話番号」を抽出し、見つからない場合は、「B」シートのA2セル～C2セルから「B001」を検索し、一致した行にある「電話番号」を抽出する。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx
例題２｜各シートから検索抽出する
- 「全会員名簿」シートの「電話番号」を求めるセル（B3セル）を選択し、「=IFNA(」と入力する。
- ［値］…VLOOKUP関数で、A3セルの検索値で「A」シートから「電話番号」を抽出する数式を入力する。
- ［NAの場合の値］…VLOOKUP関数で、A3セルの検索値で「B」シートから「電話番号」を抽出する数式を入力して、「Enter」キーで数式を確定する。
【数式】=IFNA(VLOOKUP(A3,A!A2:C3,3,0),VLOOKUP(A3,B!A2:C2,3,0))
数式の解説
それぞれの数式は、「A」シートのA2セル～C3セルから「B001」を検索し、見つかった場合は一致した行にある「電話番号」を抽出し、見つからない場合は、「B」シートのA2セル～C2セルから「B001」を検索し、一致した行にある「電話番号」を抽出する。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx
コメント