エクセルで選択したセルと同じ名前のグループ行、指定セル一つ下を抽出することはできますか？

以前、下記質問をしたのですが・・




「エクセルで下記画像の左側表E8~10、E12〜14の式がわからなくて困っております

D4とG4はプルダウンで右表と連動しています

E8~10、E12〜14に同じ路線（グループ）の前後の駅を入れたいと考えております

図ですとE11が渋谷ですのでE8~10は空白になると思うのですが、どの路線のどの駅を選択しても自動で出てくるようにすることは可能でしょうか？」





この質問に対して




左の表をSheet1, 右の表をSheet2とします。
Sheet1に次の通り入力してください

G7
=CHAR(MATCH(D4,Sheet2!B2:E2,0)+65)

G8～G14
=ROW()-11+MATCH($G$4,Sheet2!B:B,0)

E8
=IF(G8<3,"-",INDIRECT("Sheet2!" & $G$7 & G8))

E8をE14までオートフィル




ご親切な方が上記ご回答くださりましたが、他の路線で検索がきかないため再度投稿させていただきました。


こちらだとB:Bのみ検索可能で、他の路線が検索できない状態です

例えば「丸ノ内線」の「四ツ谷」等、どの路線でも検索できるようにしたいです

自身の力不足ではありますが何とぞよろしくお願いいたします

No.1 ベストアンサー 回答者： OrangeRat 回答日時： 2017/06/22 15:26

大変失礼を致しました・・・。

G8～G14
=ROW()-11+MATCH($G$4,Sheet2!B:B,0)

こちらの部分、以下に変更をお願いします。
=ROW()-11+MATCH($G$4,INDIRECT("Sheet2!"&$G$7&":"&$G$7),0)

この回答へのお礼

度々ありがとうございます・・！！！
大変助かりました！

自身のスキルアップも頑張りつつ、またお願いしてしまうかもしれませんがその際はよろしくお願いいたします！

お礼日時：2017/06/22 16:38

No.3 回答者： fujillin 回答日時： 2017/06/22 16:29

こんにちは

試みに一つの式にしてみました。

◇既回答とほぼ同じ内容
E8に以下の式を入れて、下方にオートフィル

=IF(MATCH(G$4,OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:E$2,0)),0)+ROW()>13,INDEX(OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:F$2,0)),MATCH(G$4,OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:E$2,0)),0)+ROW()-11),"")


◇駅名表の下方の終点を過ぎると「0」表示になるのを避けるのなら

=IF(AND(MATCH(G$4,OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:E$2,0)),0)+ROW()>13,INDEX(OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:F$2,0)),MATCH(G$4,OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:E$2,0)),0)+ROW()-11)<>""),INDEX(OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:F$2,0)),MATCH(G$4,OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:E$2,0)),0)+ROW()-11),"")


◇さらに、簡単なエラー処理もするなら（不要かもしれませんが）

=IFERROR(IF(AND(MATCH(G$4,OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:E$2,0)),0)+ROW()>13,INDEX(OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:F$2,0)),MATCH(G$4,OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:E$2,0)),0)+ROW()-11)<>""),INDEX(OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:F$2,0)),MATCH(G$4,OFFSET(Sheet2!A:A,0,MATCH(D$4,Sheet2!B$2:E$2,0)),0)+ROW()-11),""),"")

この回答へのお礼

す・・・すごい式ですね！
まだ私にはすごいということしか言えないレベルで恐縮です汗
わざわざありがとうございます！感謝いたします！

お礼日時：2017/06/26 12:18

No.2 回答者： isyohogo 回答日時： 2017/06/22 15:33

別路線で同一駅名がある時点でご希望の機能を実現するのは難しいと思います。

Aに路線名Bに駅名を入れるとかでBは路線間ごとに空白セルを3つ入れるとかで考え直されては？

この回答へのお礼

解決いたしました！コメントありがとうございます

お礼日時：2017/06/22 16:39