VLOOKUPで検索結果が複数あった場合の表示

質問

こんにちは。
初めて質問させていただきます。

Excelで以下のような表を作りたいと思っています。

（シート：志望校調査）
　 A　　　　　　B
1　 氏名　　 　　志望校
2　鈴木智子　　 明治大学
3　鈴木智子　　 早稲田大学
4　鈴木智子　　 慶応大学
5　鈴木智子　　 成蹊大学
↓
（シート：生徒別志望校一覧）
　　A　　　　　B　　　　C　　　　　　D　　　　　E
1　氏名　　 　　志望校1 志望校2 志望校3　　志望校4
2 鈴木智子　　 明治大学　早稲田大学 慶応大学　 成蹊大学

VLOOKUPを使って氏名を検索値にして表を作ろうと
したのですが、２件目以降のデータをどのように取得したら
よいかわかりません。
上記のような表を作るよい方法がありましたら
ご教授いただきたく、よろしくお願いいたします。

ちなみに、今作成している表の、氏名の入っているセルは
上下のセルを結合させたものであるため、配列を使った数式が
使用できませんでした。
セルの結合を解除することはできないため、配列を使用しないで
結果を得る方法があれば教えていただけないでしょうか。

よろしくお願いいたします。

回答No.2

noname#204879

シート：志望校調査
　　　　A　　　　　 B
1　 氏名　　　　志望校
2　 鈴木杏樹　　阪神大学
3　 鈴木杏樹　　誠心大学
4　 鈴木智子　　明治大学
5　 鈴木智子　　早稲田大学
6　 鈴木智子　　慶応大学
7　 鈴木智子　　成蹊大学
8　 鈴木保奈美　成城大学
9　 鈴木保奈美　鎌倉大学
10　鈴木保奈美　堀風呂大学
11

シート：生徒別志望校一覧
　　　 A　　　　　B　　　　　 C　　　　　D　　　　　E　　　　F
1　氏名　　　　志望校1　 志望校2　　 志望校3　　 志望校4　 志望校5
2　鈴木智子　　明治大学　早稲田大学　慶応大学　　成蹊大学
3　鈴木保奈美　成城大学　鎌倉大学　　堀風呂大学
4　鈴木杏樹　　阪神大学　誠心大学
5

シート「生徒別志望校一覧」において、
１．セル B1、B2、B3、… に数値 1、2、3、… を入力して、[ユーザー定義]で
　　　　"志望校"G/標準
　　に書式設定
２．セル B2 に次式を入力
　　=IF(OFFSET(志望校調査!$A$1,MATCH($A2,志望校調査!$A$1:$A$100,0)+B$1-2,0)=$A2,OFFSET(志望校調査!$A$1,MATCH($A2,志望校調査!$A$1:$A$100,0)+B$1-2,1),"")
３．セル B2 を右方に（セル F2 まで）ズズーッとドラッグ＆ペースト
４．範囲 B2:F2 を下方にズズーッとドラッグ＆ペースト

回答No.1

mshr1962

A　　　　　B　　　　C
1　 氏名　　 No.　　　　志望校
2　鈴木智子　鈴木智子1 明治大学
3　鈴木智子　鈴木智子2 早稲田大学
4　鈴木智子　鈴木智子3 慶応大学
5　鈴木智子　鈴木智子4 成蹊大学
上記B2の"鈴木智子1"を求める数式は
=A2&COUNTIF($A$2:$A2,$A2)
として下方にコピー
結果でB2の"明治大学"を求める数式は
=IF(COUNTIF(志望校調査!$A:$A,$A2)>=COLUMN()-1,VLOOKUP($A$2&(COLUMN()-1),志望校調査!$B$2:$C$100,2,FALSE),"")
として左方にコピー
※同姓同名がいなければこれでいいかと・・・

お礼

早速ご回答くださり、本当にありがとうございました！
教えていただいたとおりに書いてみたら、思い通りの
結果が返ってきました。
とても助かりました！ありがとうございました！