Get last n row data from Columns in another sheet based on cell value using drop-down list

Hello Seniors, and Talents,

Attached is sample workbook, wherein;

Sheet 2 is Input (RawData) from where I want to retrieve data in Sheet1 as follows:

Sheet2 looks like -
Student.jpg

1) In Sheet1, Cell B11 currently I am using Data validation list (drop down list), from where I can select student name from range Sheet2!B2:K2. This could extend to column L or upto z or so (possibly sometime more than 200 students!), so flexibility is required.

2) Based on Cell B11 cell value, where I select student name from Sheet2, I want to retrieve corresponding student name, LAST 6 TRANSACTIONS as shown in attached workbook, i.e. in range Sheet 1 ! C12:D17.
StudentScore.jpg
3) Please do note that in Sheet2, such dates, and marks will keep adding at bottom in rows, so I want to retrieve only last six record from bottom.

4) Just to make it visually clear, I have manually shown last 6 row data corresponding to Student named Julie from Sheet2 in Sheet1! C12:D17

5) Seek Help, how to avoid two appearance of same student name. If I put common name across two columns (deleting data from Sheet2 in cells C2, E2, G2 etc I get blank space under dropdown list in between two student name.

What combination of OFFSET or MATCH/INDEX I should use to get above desired result. I tried several ways, but somewhere I am going wrong on concepts.

How to keep formula flexible, such that, if student names expands in columns , and data gets added in rows, it gives the desired result.

I don’t want VBA Macro code for above. Thanks.

I keep switching between MS-Excel 1997-2003, so sometime using Combo Box made in higher version gets deleted, so am not using that but preferring data validation approach in cell to retrieve student name. So, please help, that it should work across possibly all version.