Apply percentage based on number range and criteria

Good day

I have been doing some searches and quite frankly I am not 100% sure what I even should be searching for... I think range and arrays... it might even be a case of needing code verses a formula...

I have attached a sample of what I am looking for. In practice it seems pretty simple, but I would like to automate this function to eliminate the manual calculations required...

I want to be able to have a column(s) of a worksheet "search" for a "range of numbers" and once it is found the correct range apply a percentage multiplier from one of (in this case) two columns based on a criteria (ResourceCode).

In my sample I am using SubTotal1 as the column that contains the number I am searching for. For the argument that number is 10.02. I would like to "search" columns From (ColA) and To (ColB) and it would find a "range" that 10.02 falls within; for the argument lets say that range is 10.0 to 10.99. In the next column I have Resource1 (ColC) and Resource2(ColD) with the increase multiplier; for the argument lets say 2.0 and 3.5. If the criteria is Resource1 then the 10.02 would be multiplied by 2.0. If the criteria is Resource2 then the 10.02 is multiplied by 3.5.

Perhaps my sample is easier to understand then my attempt and trying to "verbalize" my request.

I don't mind trying to figure this out for myself, but looking for some clues as where to start.

Any assistance is appreciated.

Cheers

John

Hello DMG

Thank you for this. I see that you eliminated the "To" (ColB) and left the "Range" just in ColA, that makes sense to me because the "range is still the same.

I will research this INDEX and MATCH functions and try to learn more.

Your help and sample is appreciated.

Cheers

John