Create billing tool

I am working on a tool that helps us review shipping bills. I specifically need help with the part labeled Base Rate Charge Identifier.

The top portion of the tool we input information about the bill, and it pulls from a separate table that lists what the charge should be, based on Zone, Weight, Shipping Class to find the listed rate. The zones go 100-150 with each 5 being a Tier (101-105 = Tier A, and 140-150 capping at Tier I)

The Shipping bill portion we input when we find we were incorrectly charged more than the advertised rate. The table to the left is a sample of the data that's kept in a separate sheet. Columns D-K are based upon weight, with D and E being a minimum charge, they are exceptions to the rest. So what I need is for N16 to only search within the class range and only the column of the listed weight to see if it matches the "Difference in Charge" in cells above it. Then I need Q16 to find the that "Tier" from column C. I hope this makes sense. Do I make these shipping classes separate tables or keep them together? If they do need to be separate what is the best way to go about making this work? Any help is greatly appreciated.

Are you still using Excel 2016?

Yes I am unfortunately

That's OK, but it would help if you could please annotate the workbook with the details you've given in post #1 so that it's absolutely clear what relates to what and where it is to come form. Thanks.

Sorry, I updated it. I hope it's more clear.

That looks better - thanks. One question, though. You said this:

I need this cell to find G25 specifically from the classs range entered into P16

but the cell highlighted in blue is F25, so is this a typo?

OK.

When we have a discrepancy from the bill received we manually input the Rate into Q8

OK - I can see this. But you then go on to use Q9 and Q4: where do the values in Q9 come from? Are they entered manually?

Mmm. I am not completely following this - sorry. I'll have another look later.