VLOOKUP help with multiple values to consider
I am creating an automated fuel surcharge spreadsheet for my company and need a little help. The fuel surcharge rate is based on the nationwide average diesel price and there is a range that each price fits into to establish our rates.
Example: current rate is $3.783 per gallon. Our rate sheet says that if the current rate is between 3.750 - 3.799 we charge 50% for LTL and $0.52/mile for TL (acronyms not important).
What I need is for it to search greater than or equal to on one field (3.750) and if true, search less than or equal too on the other field (3.799) and if both are true, select the rates for the appropriate cell. I tried =VLOOKUP($D$2,'BTI FSC'!A3:A84>=FSC!D2,4,1) but then everything after the TRUE statement also says true so I need to find a way for it to look at both to determine the value.
explination of the formula that I tried: =VLOOKUP($D$2**cell that gets average price of 3.783**,'BTI FSC'!A3:A84>=FSC!D2 **formula to determine correct alue**,4**column that is to be selected**,1**obviously TRUE**)
Any Ideas? Is there a way to stop at the first true statement?