Look up date & rank the corresponding value

Hi - I am trying to write a function that will look up a date and rank the corresponding value as either 1, 2 or 3, depending on where that value sits within a range

Basically I want the function to do the following…

1, look up date in sheet & and compare it to the dates in sheet 2
2, if the date matches ie 1992 = 1992, then compare the values in column B to the data in columns C, E & G in sheet 2 & rank the data in column B sheet 1lookup and rank.xlsx
3, if the value is between 0 & the value in C then rank as 1, if the value is between C+1 and E, then rank as 2, if the value is between E+1 and G then rank as 3.

Attached is an excel sheet that will hopefully clarify what I am trying to do. The values in red is what I would like the function to derive.

Please let me know if you need me to clarify anything further.