データの抽出する関数について

質問

データシートにデータが入っています。
A B　　　C　D・・・・続く
1 鈴木 りんご
2 鈴木 みかん
3 渡辺 りんご
4 鈴木 りんご
5 山田 バナナ
6 渡辺 バナナ
続く
氏名シートのA2のセルに氏名を入れると、その氏名のデータを抽出するように式を考えていますがうまくいきません。
=IF(ISERROR(INDEX(データ!A1:$I$15,MATCH($A$2,データ!A1:$A$15,0),2))=FALSE,INDEX(データ!A1:$I$15,MATCH($A$2,データ!A5:$A$15,0),2),"")
とA4に式を入れて、A2には鈴木と入れて試みると、鈴木は正常に全件抽出できても、渡辺や山田はMATCHのデータ範囲のせい？で正常に抽出できません。
どのような関数にすればいいかどなたかご教授ください！

回答No.2

imogasi · ベストアンサー

例データ
Ｓｈｈｅｔ３の
Ａ２：Ｃ１０に
Ａ列　　Ｂ列　　　Ｃ列　　Ｄ列（計算結果）
鈴木 りんご 10 1
鈴木 みかん 20 2
渡辺 りんご 10 2
鈴木 りんご 30 3
山田 バナナ 40 3
渡辺 バナナ 50 3

Ｄ２に=COUNTIF($A$1:A2,"鈴木")と入れてＣ７まで複写。
Ｓｈｅｅｔ４に行って
Ａ２に
=INDEX(Sheet3!$A$1:$C$10,MATCH(ROW()-1,Sheet3!$D$1:$D$10,0),0)
と入れてＡ４まで複写。
Ａ２：Ａ４をＣ列まで複写。
結果Ｓｈｅｅｔ４の
Ａ列　　Ｂ列　　　Ｃ列　
鈴木 りんご 10
鈴木 みかん 20
鈴木 りんご 30
これにはＳｈｅｅｔ３とＳｈｅｅｔ４の式に手抜きがありますが
質問者のレベルならお分かりと、対策を略してます。
＝ＩＦ（Ａ２＝””，””，・・・）や=IF(ISERROR(・・・)で逃げてください。（imogasi方式）
ワーク列を使うことによって、配列数式を使うのに比べ判りやすくはなってます。

お礼

imogasiさんの方法で行いました。
ワーク列への設定と+以下の関数で出来ました。
=IF(ISERROR(INDEX(データ!$A$5:$I$124,MATCH(ROW()-15,氏名!$J$16:$J$45,0),2))=FALSE,INDEX(データ!$A$5:$I$124,MATCH(ROW()-15,氏名!$J$16:$J$45,0),2),"")
ありがとうございます！！

回答No.1

Wendy02

こんにちは。

配列確定をしないで、取り出す方法を考えてみました。
（ただし、配列は使っています。）

=IF(COUNTIF(データ!$A$1:$A$15,$A$2)>=ROW(A1),INDEX(データ!$A$1:$I$15,SMALL(INDEX((データ!$A$1:$A$15=$A$2)*ROW($A$1:$A$15),,),ROWS($A$1:$A$15)-COUNTIF(データ!$A$1:$A$15,$A$2)+ROW(A1)),2),"")

ちょっと解説:
IF(
COUNTIF(データ!$A$1:$A$15,$A$2)>=ROW(A1),～,"")
これは、同じ検索値がいくつあるか数えて、それ以上の行はエラーが出るので、""で空白にします。

INDEX(データ!$A$1:$I$15, ～,2) は、通常のINDEX関数

SMALL(INDEX((データ!$A$1:$A$15=$A$2)*ROW($A$1:$A$15),,)
同じものがある場合に、その行番号を小さい順から並べます。（ただし、False のところは、０になっています。）
中に、INDEX があるのは、配列を一旦、標準で扱えるように変換しています。

,ROWS($A$1:$A$15)-COUNTIF(データ!$A$1:$A$15,$A$2)+ROW(A1))

SMALL関数の中の０の数を数え、それより大きい数値の順位の数値を、１つずつ、取り出します。

このような考え方で作られています。