+ Post New Thread
Results 1 to 10 of 10
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 ...
  1. #1

    Join Date
    Jul 2012
    Posts
    4
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    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?

    Thanks,

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

  2. #2


    Join Date
    May 2009
    Posts
    2,878
    Thank Post
    258
    Thanked 767 Times in 582 Posts
    Rep Power
    269
    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 :

    3.750,.5,.52
    3.800,?,?

  3. #3
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    922
    Thank Post
    56
    Thanked 161 Times in 113 Posts
    Rep Power
    67
    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.

  4. #4

    Join Date
    Jul 2012
    Posts
    4
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Thanks for the prompt reposnse.

    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

  5. #5
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    922
    Thank Post
    56
    Thanked 161 Times in 113 Posts
    Rep Power
    67
    Quote Originally Posted by clint6998 View Post
    Thanks for the prompt reposnse.

    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])

  6. #6

    Join Date
    Jul 2012
    Posts
    4
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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...

  7. #7
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    922
    Thank Post
    56
    Thanked 161 Times in 113 Posts
    Rep Power
    67
    Quote Originally Posted by clint6998 View Post
    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

  8. #8

    Join Date
    Jul 2012
    Posts
    4
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.
    Attached Thumbnails Attached Thumbnails VLOOKUP help with multiple values to consider-untitled-1.jpg   VLOOKUP help with multiple values to consider-untitled-2.jpg  

  9. #9
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    922
    Thank Post
    56
    Thanked 161 Times in 113 Posts
    Rep Power
    67
    Quote Originally Posted by clint6998 View Post
    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

  10. #10

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    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...
    Last edited by CESIL; 24th July 2012 at 10:34 PM.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 5
    Last Post: 1st July 2014, 08:34 AM
  2. Help with file transfer to Ipad2
    By Templar in forum AV and Multimedia Related
    Replies: 8
    Last Post: 13th January 2012, 09:53 AM
  3. Replies: 0
    Last Post: 12th July 2011, 07:16 PM
  4. Any Backup Exec. experts to help with this one...
    By kennysarmy in forum Windows
    Replies: 4
    Last Post: 11th February 2008, 03:24 PM
  5. Replies: 1
    Last Post: 8th June 2007, 08:43 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •