Need an Excel Formula

[Scott02]

Hello All,

I was asked for help with something and at this time I can't come up with an answer. We need a formula that looks at a weight in pounds, and compares it to ranges to determine what shipping should be. Here's an example of it.

Weight Weight Range Shipping
5 0-10 3
25 10.01-20 5
12 20.01-30 6
This is just an example. There are more weight ranges to the table, but the formula should return a "3" for the 5 weight, "6" for the 25 weight, etc. Pretty straight foward, but I can't figure out how to compare the weight to the ranges. If anyone can help, I'd appreciate it.

Scott

 

[WebNickson]

Use vlookup. Read the help on how to use this function.

rgds,
Nickson

 

[KenWright]

I prefer INDEX for this one as I just find it easier to get it to look at the next higher value in the list. With your data as follows in A1:B8:-

Weight Price
999 20
100 15
50 11
40 9
30 7
20 5
10 3

and your list of weights in say A10:A100

in B10 put the following formula and then copy down to B100

=INDEX($A$2:$B$8,MATCH(A10,$A$2:$A$8,-1),2)

or (Just another version)

=INDEX($B$2:$B$8,MATCH(A10,$A$2:$A$8,-1))

Note that the Weight list MUST be sorted in Descending order.

Regards
Ken..............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

If you do wanna use vlookup - this is how to set it up:
Weight Price
0 3
10.01 5
20.01 7
30.01 9
40.01 11
50.01 15
100.01 20

Same setup as Ken - enter this in B10
=vlookup(A10,$A$2:$B$8,2,TRUE)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Scott02]

Thanks for the replies. I've used both the vlookup and index formulas, but always for a specific value. I did not realize it would work for a number within a range. I tried the Index forumla first and it worked perfectly. Thank you again and have a great day.

Scott

 

[KenWright]

VLOOKUP takes an optional 4th argument of TRUE or FALSE. Omitting it means that it defaults to TRUE. When used with TRUE it will search for the closest match, whereas when used with FALSE it needs to be an exact match.

If used with TRUE then the data needs to be sorted in Ascending order as per Geoff's example, whereas if used with FALSE there is no need for any order.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------