Retrieving data from the preceding rows w/ Hlookup

Question

first time posting so Im hoping for some assistance.

I have a table of data, and the primary key for the Hlookup is sitting of the 3 row down. I need a Hlookup to retrieve the data above.

For example...

Row 1 Science

Row 2 Period 1

Row 3 Eric

The data cannot be changed due to the nature of the spreadsheet. But I need to build a timetable based on particular names and the information associated to that person.

So Eric is teaching Science in Period 1. That needs to be added to a seperate sheet. Outcome

Eric

Science

Period 1

A Hlookup would be perfect if it were possible to retrieve the preceding rows.

Is there anyway to do this?

Thanks in advance!

IMAGE GOOGLE SHEETS

Answer 1

Index()/Match() combination may work for you. Try below-

E3 cell formula =INDEX(A:A,MATCH(E2,A:A,0)-2)

E4 cell formula =INDEX(A:A,MATCH(E2,A:A,0)-1)