• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Look up a value within a range

F

fjo2818

New Member
I want to test if a value falls between a range of numbers and then give me the resulting value assigned to that range. This is for a shipping tariff. For example:


Weight = 2000 lbs

Weight Range Rates

0 - 1500 lbs = $19

1501 - 2500 lbs = $38

2501 - 3500 lbs = $76


I want to look up the value "2000" and it should return a value of "$38"

What is the easiest way to accomplish this? My first thought was to use VLOOKUP and create a table containing every value but this would lead to a table with over 10,000 lines. There seems to me that there must be an easier method to accomplish this.
 
Hi ,


You are right that a lookup table will do the job ; however , you are wrong when you think that this would lead to a table of 10,000 lines.


What you need a table for is your table of tariffs ; if there are 50 slabs , you will end up with a maximum of 50500 lbs ; 100 slabs will give you a maximum of 100500 lbs.


Depending on what your actual maximum requirement is , you will need so many slabs.


The table can consist of 2 columns , the first column having the lower limits such as 0 , 1501 , 2501 ,... the next consisting of the tariffs such as 19 , 38 , 76
 
Hi fjo2818


Welcome to the forum.


As mentioned by NARAYANK991, you can form the table as follows for the slabs.


Weight (Col A), Tariff (Col B)

0, 19

1501, 38

2501, 76


Then the following formula can be used to look up the value.


=INDEX($B$1:$B$3,MATCH(A8,$A$1:$A$3,1))


Amritansh
 
NARAYANK991 - I am not sure what you mean by "slabs"

How I envisioned the VLOOKUP table would be Column A 0 - 10,000 (10,000 lbs is the maximum weight) Column B would have the associated tariff value - in other words $19 for every value (from column A) between 0 and 1500. This is why I assumed there would need to be 10,000 lines in that table.


The weight ranges are as follows:

0-1500 $19.00

1501 - 2500 $38.00

2501 - 3500 $57.00

3501 - 4500 $76.00

4501 - 5500 $95.00

5501 - 6500 $114.00

6501 - 9999 $138.00


So to be clear, when I get a shipment I am told the weight of the shipment, for example 2000 lbs. I will enter that value in a cell and in the next cell I want it to look up the correct tariff amount.


Does this make sense?
 
Hi fjo2818


You don't need to have all the values as with the MATCH function you can search for value less than the particular value. So you will only have to make a table with boundary conditions i.e. 0, 1501, 2501, 3501, 4501, 550 and 6501. Then you can use the formula mentioned earlier.


Amritansh
 
@Fjo2818


Hi


Please check this formula if your data in a2:c8


and you mention the Weight in e2 use the formula in f2 as


=VLOOKUP(E2,A2:C8,3)


OTHER WISE try to download the file


https://dl.dropbox.com/u/75654703/Fjo2818.xlsx


Thanks


SP
 
sgmpatnaik,

I looked at the file you sent, and that's exactly what I want it to do. I am not familiar with the cell format you used in column C for the tariff. The result comes back as "Rs__" I changed the cell format to "Accounting" and it does not appear to affect the result. However, is that cell format required? If not, I think this is what I need!!

Thank you very much!! I love this website!!

FJO
 
@Fjo2818


Hi


Glad we could solve your problem, no it is not required the cell format if you don't want to specific the currency symbol then you can remove them from format cells follow the instruction


1. select the cells


2. right click


3. select the format cells from bottom side


4. select the accounting in general section


5. in symbol option you can give None


6. now the symbols are removed from the Column C


welcome back if any problem


Thanks


SP
 
Dear Fj2818


You can use a UDF (user definied function) to get rid of this. I hope it will solve your problem. Just copy the below code, and create a module and paste it


Function getRate(rng As Range) As Integer

Dim Weight As Long


Weight = rng.Value


Select Case Weight

Case 0 To 1500

getRate = "19.00"

Case 1501 To 2500

getRate = "38.00"

Case 2501 To 3500

getRate = "57.00"

Case 3501 To 4500

getRate = "76.00"

Case 4501 To 5500

getRate = "95.00"

Case 5501 To 6500

getRate = "114.00"

Case Else

getRate = "138.00"

End Select
 
You must log in or register to reply here.
Back
Top