IFS statement and Lookup Function?

Hi there,

I'm trying to work out the 'rise of floor' of certain buildings on my Excel spreadsheet. As all buildings have different number of floors, I'm trying to come up with a formula based on what the subject level is, it categorises it as a low, mid, or high-rise floor based on the criteria listed in SHEET 2 (second spreadsheet/tab).

I've tried combining an IFS and VLOOKUP formula but no luck so far. I want to utilise a formula that will allow me to click and drag as I will have a big list to analyse.

I've attached the spreadsheet for reference.

Many thanks in advance.

Workbook 1.xlsx