エクセルの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.8 回答者： mike_g 回答日時： 2020/07/29 14:27

馬鹿の一つ覚へ的なVLOOKUPを使は無い別解です。

邪魔臭い範圍 C3:C16 を空白にして居ます。(*^_^*)

添附圖參照
１．<前同>セル C2 を選擇⇒Alt+AVV⇒[入力値の種類]として“リスト”を選擇⇒[元の値]ボックス内にマウスカーソルを放り込んで、範圍 B4:B12 を撫で撫で⇒[OK]
２．範圍 A3:I500 を選擇⇒Alt+MC⇒“上端行”だけにチエック入れ⇒[OK]
３．セル G3 に次式を入力
￣￣=IFERROR(INDEX(ｺﾒﾝﾄ,SUMPRODUCT((学籍番号=C1)*(受験期=C2)*ROW(学籍番号))-3)&"","")

この回答へのお礼

詳細なご指示ありがとうございます。図もあり大変分かり易いです。
作業手順１は実施できました。リストを作るのにこんなに便利な方法があったのですね！一つ勉強になりました。

ところが、作業手順２を実施しようとすると、「現在の’B'の設定と置き換えますか？」というメッセージが表示され、はいを選んでもいいえを選んでも同メッセージが消えず、キャンセルするしかない状態です。
どうすればよいか引き続きご教示いただけますでしょうか。

お礼日時：2020/07/29 15:08

No.7 回答者： mike_g 回答日時： 2020/07/29 14:10

添附圖參照

１．セル C2 選擇⇒Alt+AVV⇒[入力値の種類]として“リスト”を選擇⇒
￣￣[元の値]ボックス内にマウスカーソルを放り込んで、範圍 B4:B12 を
￣￣撫で撫で⇒[OK]
２．セル G1 に次式を入力
￣￣=IFERROR(VLOOKUP(C1&C2,C4:I16,7,FALSE)&"","")

No.6 回答者： banzaiA 回答日時： 2020/07/29 12:22

G1のセルに、

=INDEX(SORT(FILTER(A4:I250,(A4:A250=C1)*(RIGHT(B4:B250,1)="期")*(I4:I250<>""),""),2,-1),1,9)
でどうですかね。

この回答へのお礼

すみません。コピー&ペーストでやってみたのですが、残念ながら、この関数はエラーです、というメッセージが出てきてしまいました。
ご教示ありがとうございました。

お礼日時：2020/07/29 15:06

No.5 回答者： 瀬戸の花婿 回答日時： 2020/07/29 08:39

学籍番号列と受験期列の間に受験期コード列を入れるだけで幸せになれそうです。

No.4 回答者： mike_g 回答日時： 2020/07/28 20:52

》 （←これがあるためにXLOOKUPが使えません…）

「これ」とは「不要データ」と云ふ文字列データの事ですか？
呈示された“寿限無”式を解讀する元氣は無いし、正常に「動いてはいる」のなら囘答者にヒケラカス必要も無いでせう。
其れより貴方が期待する式の戻り値だけを何処にどの樣に表示させるのかを明記されたし！
「これ」が“該当データ”とか“空欄”であれば、だう成るンですか？
私の添附圖の[摘要]欄で、計算に必要なデータはドレドレですか？

No.3 回答者： banzaiA 回答日時： 2020/07/28 20:01

提示されたイメージ図でいうと

学籍番号001の学生は、どのデータを持ってくればいいのですか？
具体的な参照元と期待される表を示して下さい。

No.2 回答者： satoumasaru 回答日時： 2020/07/28 19:13

いくつかの作業セルの列をつくって、いくつかに作業を分割した方がよくないですか？

そしてその作業セルの列を非表示にすれば、使い勝手はかわりません。

この長い関数の列をみたらクラクラとします。

No.1 回答者： mike_g 回答日時： 2020/07/28 18:13

》 「A5セルと同キーの最終行の該当セル」

「同キー」の意味は？

》 XLOOKUPの「検索方法-1」
XLOOKUPって何の事？貴方の Excelのバージョンは何？
因みに私のは Excel 2019 です。