I'm trying to build a worksheet that will be able to calculate amounts based
on several rate levels.

Desired end result: Worker types in time (3:53pm) in cell (I'll call it F2
(Monday)). In cell G2, which will be hidden, there will be a formula that
brings back a rate multiplier. This rate multiplier will be multiplied by an
hourly rate that is summed in the last column. These formulas will be
applicable to each day of the week.

Work completed thus far: Tab 1 is my primary sheet where the work will be
completed. Column C is my Rate Code (R1 = $5, R2 = $4, etc.). Column D has
a Lookup function (which is working like I intended). Tab 2 contains my Rate
table from which the values are being pulled. Columns E, G, I, K & M will
have the data entry point. Columns F, H, J, L & N will have the formulas
that bring back the correct rate multiplier. Column O will total the amounts
calculated each day based on the Rate Code and the time that was entered.
Now for where I'm stuck.

I think I need an IF statement that will look up my rate multipliers;
however I'm not sure how to do for a range of numbers. For example, the
range of timescale one (Time1) is 3:31 - 3:45. The multiplier for any times
entered within this range should be 0. Time2 is 3:46 - 4:00, with a
multiplier of 0.25). So in my example above, if someone enters in 3:53 and
their Rate Code is R1, then the result should bring back a multiplier of
0.25. I would then tack on additional work for it to multiply that amount
times the $5.

Any help is greatly appreciated!!