エクセルで複数のシートからのデータ抽出

質問

• エクセルで複数のシートからのデータ抽出する方法を知りたいです
• 同じ氏名を含む人の情報をリストにするため、複数のシートからデータを取得する方法を教えてください
• 製品名や氏名で検索し、関連する情報をリストにするためのエクセルの機能を教えてください

回答No.3

bunjii

>複数シートのCOUNTIFとVLOOKUPで出来そうな気がしたのですが思うようにいかず。
VLOOKUP関数で抽出するのは無理なのでINDEX関数とLARGE関数、COUNTIF関数の他幾つかの関数を組み合わせれば可能です。
Excel-2010で検証してみましたが（検索シート）のA列にあるNo.が提示の画像に符合できませんので他の項目のみでしたら下記の数式で対応できました。

Sheet3!B5セルへ数式を入力して右と下へオートフィルコピーしました。
=IFERROR(INDEX(Sheet1!$B$3:$E$22,LARGE(INDEX((FIND($B$2,Sheet1!$B$3:$B$22&$B$2)<LEN(Sheet1!$B$3:$B$22))*Sheet1!$A$3:$A$22,0),COUNTIF(Sheet1!$B$3:$B$22,$B$2&"*")-ROWS($5:5)+1),COLUMNS($B:B)),IFERROR(INDEX(Sheet2!$B$3:$E$22,LARGE(INDEX((FIND($B$2,Sheet2!$B$3:$B$22&$B$2)<LEN(Sheet2!$B$3:$B$22))*Sheet2!$A$3:$A$22,0),COUNTIF(Sheet2!$B$3:$B$22,$B$2&"*")-ROWS($5:5)+COUNTIF(Sheet1!$B$3:$B$22,$B$2&"*")+1),COLUMNS($B:B)),""))

Sheet3!のA列の条件を明示して頂ければ数式の修正を試してみます。

お礼

早々のご回答、それも大作をありがとうございます。

但し、当方のスキルとこれまでの実績？からこれを当方が動くように出来るまでには相当の時間が掛かる事は自信があります。

残念ですが如何ともしがたい現実とご提案の関数は諦めます。

補足

当初はもっと簡単に関数で出来ると思ったのですが。
最初に考えたのは、両方のシートのＡ列に通し番号（No）を振って氏名の一部を、
「No＆＊氏名＊」でVLOOKUPで引っ張ればできるのでは？
と思ったのですが、式が長くなってしまって（それでもご回答の半分以下程度）何度も最初からやり直したのですがうまく行かず。

連番だと２枚のシートにまたがるので、シート①とシート②でCOUNTIFで＊氏名＊の個数を連番にすれば出来る？とかトカ・・・

結局長い式には頭が付いて行かず断念した次第です。