Please help writing a formula based on the following table!

[YUNAI]

Weight Lbs Shipping
1 to 7 Lbs $14
8 Lbs $16
9 Lbs $18
10Lbs $20
11 Lbs $22
12 Lbs $24
13 Lbs 26
14 Lbs $28
15 Lbs $30
16 to 110 Lbs $2 per additional lb

[Xandler]

Uh...some context would help. What exactly do you want the formula to do. This forum does not have mind readers.

[MrShorty]

I see a lookup table somewhere in that list of text, which suggests a lookup function (maybe VLOOKUP() https://support.office.com/en-us/art...8-93a18ad188a1 ) to access information in the lookup table.

[YUNAI]

I have the excel file but when I click on the clip button to attach it, nothing happens.....

[YUNAI]

I want a formula to give the correct value from that table. If the package weights between 1 and 7 lbs, the charge is $14. If the package weights 8 lbs the charge is $18, if the weight is 9 lbs the charge is $20 and so forth and so on up to 15 lbs with a charge of $30. Then when the package weights between 16 and 110 lbs, the charge is $2 per additional pound.

The formula takes a value from a specific cell and calculates the charge according to the weight entered in that cell.

I appreciate any help

Thanks!

[MrShorty]

Did you have a look at the help file for the VLOOKUP() function? What you describe is exactly what a lookup table + lookup function should do. In your case (contrary to MS's examples), you will want the 4th argument of VLOOKUP() to be TRUE, so spend some time with the help file to understand how that changes the lookup algorithm.

What part(s) of building a lookup table are you having trouble understanding? What parts of using the VLOOKUP() function are you having trouble with?

[YUNAI]

I think I understand Vlooup(), let me ask you a dumb question if you don't mind, a table with 110 rows must created to use VLOOKUP() to accomplish what I want? It might be just as simple as that. Creating a formula with a bunch of IF, AND, ELSE statements might be overthinking a simple task.

By the way, thanks for quick reply!

[MrShorty]

a table with 110 rows must created to use VLOOKUP() to accomplish what I want?

If you use FALSE for the 4th argument, then you probably will need 110 rows. However, if you use TRUE for the 4th argument, then you should only need the same 10 rows you show in post 1. The lookup table would look something like:

Please Login or Register  to view this content.