How to efficiently model this data set?

[g3lo18]

Hi,

I am pretty decent with Excel, but wanted to know if there is an efficient way of modelling this, without having numerous if statements and vlookups.

Scenario:

What I have:
- Data Table containing amounts labeled by a chronological key 1 through 100 with letters assigned to each amount.
- Table that shows 3 thresholds. Low, Med, High for each letter

What I want to do:
- I want to create a formula that would look up each key (1 through 100) and take the associated letter and look up the table to see which threshold it falls under. If it is higher or equal to high threshold I want it to produce a value of 4. If it is between medium and high, produce a value of 3. If it is between low and medium to produce a value of 2. If it is lower or equal to low, produce a value of 1.

I am constraining myself to use the following:
I have been using HLOOKUP, VLOOKUP, OFFSET function, but unable to figure out an efficient way to formulating this.

Thank you kindly for your help,

I have attached an example data set for reference below:
https://we.tl/t-J8Hdk3CkFD

 

[g3lo18]

INDEX as well!