General Chat Thread, VLOOKUP help with multiple values to consider in General; I am creating an automated fuel surcharge spreadsheet for my company and need a little help. The fuel surcharge rate ...

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?

Thanks,

Clint

Last edited by clint6998; 24th July 2012 at 04:28 PM.

I think the vlookup will do what you want, you just need to arrange the data efficiently. If you are looking up nearest value, you must sort the data into ascending order and the lookup will find the row that is greater than or equal to the key but less than the next key. So organise your table into appropriate rows :

Without seeing the file to better understand what you are trying to do, have a look at using INDEX and MATCH function wrapped in 2 IF statements. MATCH allow you to do greater than/less than matching.

It is sorted in accending order. the problem is that as soon as I put the =VLOOKUP($D$2,'BTI FSC'!A3:A84>=FSC!D2,4,1), it turns the results to a true or false instead of a number and then every true value is represented as true (ex in order false, false, false, true, true, true, true, true, true, true) so I am not getting it narrowed down to just one field bcz there are multiple that are over the test value. what about using the AND function within the vlookup so that two statements have to be proven true?

It is sorted in accending order. the problem is that as soon as I put the =VLOOKUP($D$2,'BTI FSC'!A3:A84>=FSC!D2,4,1), it turns the results to a true or false instead of a number and then every true value is represented as true (ex in order false, false, false, true, true, true, true, true, true, true) so I am not getting it narrowed down to just one field bcz there are multiple that are over the test value. what about using the AND function within the vlookup so that two statements have to be proven true?

Thanks again,

Clint

Your VLOOKUP syntax is incorrect as the second paremeter is a range of cells not an equation
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

You could write it as a IF statement

=IF('BTI FSC'!A3:A84>=[Lower Value],IF('BTI FSC'!A3:A84<=[Upper Value],[DO Something when in range],[Do Something when above upper range]),[Do Something if below lower range])

The problem with using IF statements is that I have approx 15 pages and on each page, I have a minimum of 80 rows of data which means for each equation, it would have about 80 nested IF statements. Is that possible to do it that way, yes. Is it efficient, no. If the vlookup function is not the best one for what I need, do you know of one that is? Whats funny is I could do this in php and mysql in a matter of minutes, but excel is kicking me in the butt right now...

The problem with using IF statements is that I have approx 15 pages and on each page, I have a minimum of 80 rows of data which means for each equation, it would have about 80 nested IF statements. Is that possible to do it that way, yes. Is it efficient, no. If the vlookup function is not the best one for what I need, do you know of one that is? Whats funny is I could do this in php and mysql in a matter of minutes, but excel is kicking me in the butt right now...

IF statement wouldn't be possible, have a look at using INDEX and MATCH together instead of VLOOKUP

I am having a hard time understanding how those will help or how I could get them to work.

on page on, I have a value in F2 and I need a formula in B5 that takes the value of F2, and compares it to page two, column A to find the first number that is equal to or greater than F2 on page one. it also needs to check and make sure that F2 is less that the same row on page two but in column C. If both are true, then it grabs the value of column D on the same row in page two and places the value back in page one in cell B5.

I am having a hard time understanding how those will help or how I could get them to work.

on page on, I have a value in F2 and I need a formula in B5 that takes the value of F2, and compares it to page two, column A to find the first number that is equal to or greater than F2 on page one. it also needs to check and make sure that F2 is less that the same row on page two but in column C. If both are true, then it grabs the value of column D on the same row in page two and places the value back in page one in cell B5.

Thanks for the images, it makes it a whole lot easier to understand.

What your wanting to do is possible with VLOOKUP, you were close but just complicated it with the upper boundary column (d2) as you have a nature boundary between row b2 -b3 is tha same as b2 - d2.

Just use b column as the range to lookup with false as the last parameter for VLOOKUP and it will match the right row for example if the gas price was 1.61 it would match it to the 1.60 row because it would pick the closes one as long as it is less than the value trying to be matched

I was trying to come up with a coherent answer but without the sample data it was not possible...
@penfold_99 has said what I was trying and failing to say

[edit] I started to build my own data and got as far as something that looked like your second example...