HLOOKUP without any fixed array

Dear all,

Is that possible that we could have a HLOOKUP function lookup for a cell but without any array in the entire sheet?

Eg. Sheet2

A1 = Student, A2=HLOOKUP(A1, Sheet1![See where is the "Student" located], 2, false)

Trying to capture the subsequent column immediately followed by a moving target cell. The "Student" located in Sheet1 could be change from time to time, but no matter where it is, the excel will lookup that and return the corresponded column/value.

Many thanks in advance.

Just an addition, it will read multiple row immediate the target name and stop until the next row is blank. Any clue?