エクセルのvlookupについて、もっと簡単な方法がありましたらご教示ください。

学生の成績表を作成している者です。皆様のお知恵を拝借したく、質問させていただきました。

エクセルで学生の学籍番号を入力（A5セル）し、別シート（成績項目CSV）のデータをvlookupで引っ張って来たいと考えています。
学生によって試験の受験回数が異なるので、Ⅷ期を受けた学生はⅧ期の、受けていない学生はⅦ期の…と言うように、最後に受験した試験のデータを引用したいです。

ただし、「A5セルと同キーの最終行の該当セル」にはまた違うデータが入力されているため、XLOOKUPの「検索方法-1」は使えませんでした。

下記の長い関数で動いてはいるのですが、いつ何があって動かなくなるか恐ろしいので、もっとシンプルで分かり易い方法がありましたら是非ご教示ください。

どうぞ宜しくお願い申し上げます。


=IF(VLOOKUP(A5&"Ⅷ期",成績項目CSV!C:Q,15,0)<>"",(VLOOKUP(A5&"Ⅷ期",成績項目CSV!C:Q,15,0)),IF(VLOOKUP(A5&"Ⅶ期",成績項目CSV!C:Q,15,0)<>"",(VLOOKUP(A5&"Ⅶ期",成績項目CSV!C:Q,15,0)),IF(VLOOKUP(A5&"Ⅵ期",成績項目CSV!C:Q,15,0)<>"",(VLOOKUP(A5&"Ⅵ期",成績項目CSV!C:Q,15,0)),IF(VLOOKUP(A5&"Ⅴ期",成績項目CSV!C:Q,15,0)<>"",(VLOOKUP(A5&"Ⅴ期",成績項目CSV!C:Q,15,0)),IF(VLOOKUP(A5&"Ⅳ期",成績項目CSV!C:Q,15,0)<>"",(VLOOKUP(A5&"Ⅳ期",成績項目CSV!C:Q,15,0)),IF(VLOOKUP(A5&"Ⅲ期",成績項目CSV!C:Q,15,0)<>"",(VLOOKUP(A5&"Ⅲ期",成績項目CSV!C:Q,15,0)),IF(VLOOKUP(A5&"Ⅱ期",成績項目CSV!C:Q,15,0)<>"",(VLOOKUP(A5&"Ⅱ期",成績項目CSV!C:Q,15,0)),(VLOOKUP(A5&"Ⅰ期",成績項目CSV!C:Q,15,0)))))))))


参照元シートのイメージは以下の通りです

学籍番号　受験期　～～～　（15列目）
001　　　Ⅰ期　　　　　　不用データ
001　　　Ⅱ期　　　　　　不要データ
001　　　Ⅲ期　　　　　　該当データ
001　　　Ⅳ期　　　　　　空欄
　　　　（略）
001　　　Ⅷ期　　　　　　空欄
001　　　総括　　　　　　不要データ　　　（←これがあるためにXLOOKUPが使えません…）
002　　　Ⅰ期　　　　　　不用データ
002　　　Ⅱ期　　　　　　不要データ
002　　　Ⅲ期　　　　　　不要データ
002　　　Ⅳ期　　　　　　該当データ

質問者からの補足コメント

• 》「同キー」
  A5セルと同じ内容が入っている別シートのA列のセルが複数あります、と言う意味です。
  （例えば学籍番号001が全部で9行あります）
  
  》XLOOKUPの「検索方法-1」
  XLOOKUPの検索方法で「下から検索」と言うものがあり、それを使うと一番下のセルを引用できるのですが、一番下のセルのデータではなく、その次に新しいデータを引用したいのです。
  
  
  情報不足で申し訳ありません。
  Excel2016を使用しております。

  No.1の回答に寄せられた補足コメントです。 補足日時：2020/07/28 18:22

• 画像が添付できることに気付いておりませんでした。
  分かりにくい説明で申し訳ありません。
  改めて状況をお伝えいたします。
  
  画像のA列の学籍番号を利用して、I列のコメントを引っ張って来たいです。
  （C1に「202001」だけを入力し、G1に「すばらしい！」を表示したい。実際は別シート）
  
  なぜC1に「202001Ⅳ期」と入力できないかと言うと、学生によって最終受験期がバラバラなので、
  どの学生に何期と入力すればいいか分からないからです。
  
  そのため、「202001」と入力し、A列に「202001」と入力されているもの（図の4～12行）の中から、一番下から2つ目のコメント（I7)を返したいと考えました。
  一番下には「総括」行があるため、「一番下のデータを返す（xlookupの逆から検索）」は使えませんでした。
  
  （補足続きます）

  
    補足日時：2020/07/29 10:20

• 質問本文の寿限無式は、
  
  「202001Ⅷ期で検索して、あればその行のI列のデータを返してね、
  　なかったら202001Ⅶ期で検索して、あればその行のI列のデータを…
  　…（中略）…202001Ⅰ期で検索して、その行のI列のデータを返してね」
  
  と、IF検索をひたすら繰り返しているものです。
  
  とてもじゃないけれど良い状態とは言えません。力技です……。
  しかし自分にはお恥ずかしながらこれしか思いつかなかったのです…。
  
  そこで皆様のお知恵を拝借したいと質問させていただきました。
  エクセルのバージョンは2016です。
  
  どうぞ宜しくお願い申し上げます。

    補足日時：2020/07/29 10:25

• 》mike_g様
  
  詳細かつ分かり易い説明をありがとうございます。
  仮名遣いが独特でいらっしゃるので、明治時代から
  タイムスリップしてきた方にパソコンのことを習っているようで、
  なんだか楽しいです。勝手に明治先生とお呼びしています。
  
  仰る通りに「ツンツンして全名前を選択⇒[削除]⇒[OK]⇒[閉じる]」をしましたら、
  先の手順が実行できました！
  
  教えていただいた数式もちゃんと動きました。
  ただ、私の説明不足で、やりたいことをきちんとお伝えできていなかったようで…
  
  次の画像の場合、「2020001」の学生はI7の「チョベリグ！」を、
  「202002」の学生の場合はI14の「ようやった」を表示させたいのです。
  学生によって最終受験期が違うので、それぞれの最新コメントを拾って来たい、
  という訳です。
  
  またご教示いただけますでしょうか。
  Excelのバージョンは2016です。

  
  No.10の回答に寄せられた補足コメントです。 補足日時：2020/07/30 12:16

No.18 回答者： banzaiA 回答日時： 2020/08/01 13:55

蛇足の愚問ですが、

学生は、必ずⅠ期,Ⅱ期,Ⅲ期,Ⅳ期,Ⅴ期,･･･と連続で受験するものですか？
途中で何らかの理由で受けない？期があることはないですか？
例えば、Ⅰ期,Ⅱ期,Ⅳ期,Ⅴ期,･･･　と（Ⅲ期を受験しない）などのようなことはないのですか？
すでに回答が出ているようで、不要な質問ですが、まだ締め切っておられないので。

この回答へのお礼

返信が遅くなり申し訳ありません。
はい、学生は必ずⅠ期、Ⅱ期、と順番に連続で受験します。
ただ、受験開始時期が異なるために、最終受験期がばらばらになってしまいます。
今回のエクセルファイルでは、最新のデータをもとに配布資料を作りたいので、この問題を解決したく質問させていただきました。
もっと良い方法がございましたら、是非ご教示ください。宜しくお願い致します。

お礼日時：2020/08/03 12:43

No.17 回答者： mike_g 回答日時： 2020/07/31 12:59

[No.12お礼]へのコメント、

》 結合セルだと配列数式が動かないから、でしょうか…？
當りです。(ﾊﾟﾁﾊﾟﾁ…)

》…上手く動きませんでした。
其れ丈ぢャ分かンな～い、具体的に述べなきャあ～

》 これを別シートに引用する方法が分かりません…
其れ丈ぢャ分かンな～い、具体的に述べなきャあ～
「別シートに」何を何の爲にどの樣に「引用する」の？

何れにせよ、既に「目から鱗」の囘答が得られた樣で、もう結構、私は退散します。サヨナラ。

この回答へのお礼

お世話になりました！ありがとうございました！
もっと色々なことができるようになりたいので、頑張って勉強します。

お礼日時：2020/08/03 12:46

No.16 回答者： chonami 回答日時： 2020/07/31 10:58

うまくいったみたいでよかったです。

自分のテストの時に総括が集計するようになってなかったかも…と思って別案も出しましたが、お好きな方で作ってみてください。

この回答へのお礼

いくつかの方法をご提示くださりありがとうございました。
二つ目のOFFSETの方は自分が理解できていないせいか、上手く動かすことができず……、
職場のみなさんもExcelがあまり得意でないので、自分で説明できる「最終」を入れる方を
現在は使わせていただいております。
勉強のためにもうしばらく回答は締め切らずに置こうと思います。
大変お世話になりました。ありがとうございました！

お礼日時：2020/08/03 12:45

No.15 回答者： chonami 回答日時： 2020/07/31 10:24

そうか、総括にも点数が入ってたんでしたね。

G1=OFFSET(INDIRECT("A"&MATCH($C$1,$A$1:$A$21,0)),COUNTIFS($A$4:$A$21,C1,$D$4:$D$21,"<>")-2,8,,)

とかもいけるかな。
興味なかったらスルーかな。

No.14 回答者： banzaiA 回答日時： 2020/07/30 17:04

横からごめんなさい。

最初の質問文に、
＞最終行の該当セル」にはまた違うデータが入力されているため、XLOOKUPの「検索方法-1」は使えませんでした。
つまり、添付図の「202001 　総括　[点数省略] いっしょうけんめいです。」というコメント欄にデータがあるので、XLOOKUPは使えない。
ということでしたよね。
では、総括の欄のコメントを削除すれば、XLOOKUPは使えるのですよね。
簡単なこと、総括のコメントを削除すればいい。

みなさんの回答や補足コメントを拝読すると、総括のコメント欄は空白です。・・・なんか訳が分からなくなりました。
総括のコメント欄が空白ないので、質問がでてきているのに、
総括のコメント欄が空白で可なら、質問の意味がないじゃないですか。

この回答へのお礼

分かりにくい図にしてしまってすみません。「総括」のところですが、コメントが入っています。

このファイルはエクセルが苦手な先生も使うので、基本的に元データをコピペして、あとは学籍番号を入力するだけで
欲しい状態の資料がプリントアウトできるようにしたいのです…。
なので「総括」のコメントを全部消す、と言う作業は難しいです。

お礼日時：2020/07/31 10:37

No.13 回答者： d-q-t-p 回答日時： 2020/07/30 16:39

クエリでやればいいのにとは思いますが数式でやりたいんでしょうかね。

=LOOKUP(1,-ROW(4:300)/(A4:A300=C1)/(B4:B300<>"総括")/(I4:I300<>""),I4:I300)

この回答へのお礼

クエリがわからなくてすみません。
検索してみてみましたが、今すぐに使えるようになるのは難しそうでした。
時間を見つけて勉強してみます。

お伺いしたいのですが、これを別シートに引用するときは、どこにシート名を入れればよいのでしょうか。

お礼日時：2020/07/30 17:05

No.12 回答者： mike_g 回答日時： 2020/07/30 15:41

此の機会に(ドンナ機会やねん？)、添附圖を一新しました。

H2: =INDEX(ｺﾒﾝﾄ,MAX(IF((学籍番号=H1)*(ｺﾒﾝﾄ<>""),ROW(ｺﾒﾝﾄ)))-3)
【御斷はり】上式は必ず配列數式として入力の事

【三流大學超難問クイズ】
此の「配列數式」こそ、レイアウト變更の理由でした。何故か考へてみて下さい。(貴方も試行錯誤してみたらァ～？)

この回答へのお礼

結合セルだと配列数式が動かないから、でしょうか…？
御教示の通りにやってみたのですが、上手く動きませんでした。
また、これを別シートに引用する方法が分かりません…すみません…。

お礼日時：2020/07/31 10:44

No.11 ベストアンサー 回答者： chonami 回答日時： 2020/07/30 12:53

ちょっとベタな感じですが、今C列に入れているデータを結合したものは削除して代わりに以下の式を入れます。

C4=IF(AND(B4<>"総括",A4=A5,D4<>"",OR(D5="",B5="総括")),"最終","")

すると、点数の入っている最新の期のところに「最終」と入ります。総括のところには入りません。
学籍番号と「最終」をキーにしてその行のコメントを反映してくるのではどうでしょうか。

条件式、もっとスマートなものを他の方が出してくれるといいのですが。

この回答へのお礼

この方法で出来そうです！！ありがとうございます！
「D列に何もない所があったら、その一つ上のC列のセルに「最終」と入力してね」と言うことですね…？
全く思いつかなかった方法です。目から鱗でした。
これなら別シートからの引用方法もわかります。ありがとうございます。

もしかしたら他にもいい方法をお持ちの方がいらっしゃって、
そしてそれが、私ではない誰かのお役に立つこともあるかもしれませんので、
もうしばらく回答募集を続けてみようと思います。

お礼日時：2020/07/31 10:47

No.10 回答者： mike_g 回答日時： 2020/07/29 22:01

[No.8お礼]へのコメント、

私は寿限無如きのダラダラ(詳細とも云ふ)説明が嫌いなので、Alt+AVV、Alt+MC等のショートカットを多用します。
》 作業手順２を実施しようとすると、
》 「現在の’B'の設定と置き換えま
》 すか？」というメッセージが…
ですッて？何の事やらサッパリポンです。
私は手元に在る Excel 2019 に基いてコメントしてますが、貴方の Excel のバージョンは何？

イヤだけど「作業手順２」をダラダラと書くと下記の樣に成ります。

２．範圍 A3:I500 を選擇⇒Alt+MC⇒“上端行”だけにチエック入れ⇒[OK]
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
２．範圍 A3:I500 を選擇⇒[数式]⇒[定義された名前 <選択範囲から作成>]
￣￣⇒"上端行"だけにチェック入れ⇒[OK]
ネ、1.5倍くらい長～い説明に成るでしョ？
私ィ～、「'B'の設定」なんて知りません。そこで…

[数式]⇒[定義された名前 <名前の管理>]でShiftキーを抑えた儘でイッチャン上の名前とイッチャン下の名前をツンツンして全名前を選擇⇒[削除]⇒[OK]⇒[閉じる]
を實行して、最初からやり直してみたら如何かと。

この回答へのお礼

Alt+MCの手順のエラーは解決しました！有難うございました。
しかし、最終目標にまだ到達できておりません。
またお知恵を拝借できればと存じます。補足をご覧いただけますと幸いです。
宜しくお願い致します。

お礼日時：2020/07/30 12:19

No.9 回答者： banzaiA 回答日時： 2020/07/29 15:17

#6です。

XLOOKUP　が使用できると言うことで、提示したのですが。
無理でしたか。エクセルのバージョンは365だとうまくいくのですが。残念です。

この回答へのお礼

》banzaiA様
再度コメントをありがとうございました。
会社のパソコンのエクセルが2016なので、それで動かないのかもしれません。
折角教えていただいたのに、使いこなせず申し訳ありません。
御教示いただき感謝しております。ありがとうございました。

お礼日時：2020/07/30 12:19