# Formula Lookup? (Making a Freight Calculator)

1. ## Formula Lookup? (Making a Freight Calculator)

Hello All

Im fairly new to big formulations in excel

im trying to make a Freight Calculator

Where you will enter the Region(B1:F1) to a cell B11 and weight(A2:A7) into Cell B12 and it will return the rate(B2:F7) to Cell B13 multiplied by the weight

i work out how to do it with entering the Exact weight listed but if the weight is different it would return an error

so it need to find the rate between a certain range? im not sure if this makes sense

( See attached )  Register To Reply

2. ## Re: Formula Lookup? (Making a Freight Calculator)

Do you use linear interpolation for calculating the weight when it is not exact? (y = mx + b)  Register To Reply

3. ## Re: Formula Lookup? (Making a Freight Calculator)

Well thats what im trying to work out.. im not sure how its done  Register To Reply

4. ## Re: Formula Lookup? (Making a Freight Calculator)

Try this in B13: =INDEX(\$B\$2:\$F\$7, MATCH(B12, \$A\$2:\$A\$7, 1), MATCH(B11, \$B\$1:\$F\$1, 0)) * B12

Or, if you select cells A1:F7, and then in the Names box, left of the Formula bar, type "Table" (sans quotes), you can use this:

=INDEX(Table, MATCH(B12, INDEX(Table, 0, 1) ), MATCH(B11, INDEX(Table, 1, 0) ) ) * B12  Register To Reply

5. ## Re: Formula Lookup? (Making a Freight Calculator)

Thanks SHG

i think that did it!

if i want it on another sheet mainly to hide the table
would be something like this?

=INDEX(Sheet1!Table, MATCH(B12, INDEX(Sheet1!Table, 0, 1) ), MATCH(B11, INDEX(Sheet1!Table, 1, 0) ) ) * B12  Register To Reply

6. ## Re: Formula Lookup? (Making a Freight Calculator)

This assumes you are linearly interpolating between the weight values. I hope it works for you.  Register To Reply

7. ## Re: Formula Lookup? (Making a Freight Calculator)

Hey Everstrivin

thats really cool how that works

but the freight rate dose not change to the weight it just works on the range

eg.
501 to 1000 is at .134

so 564 will still be .134  Register To Reply

8. ## Re: Formula Lookup? (Making a Freight Calculator)

Do you need me to fix it?  Register To Reply

9. ## Re: Formula Lookup? (Making a Freight Calculator)

Mike,

If not, post your workbook as you wish it arranged.  Register To Reply

10. ## Building a Freight Calculator

I need assistance in finishing my project to build a freight calculator for my company. I have a week to finish and I am quite slow in formula. Please help.

The database I built is in RateData tab, and i need to simplify into each table for the full load rate and half load rate. I am confused to get the lookup formula work. I need to match the carrier, from, and to with FTL or LTL and then looking up for the rate value in the total (for Full Load Rate) or single space rate (for half load rate).

Thank you so much for helping me out.

Cheers
Desperato  Register To Reply

11. ## Re: Formula Lookup? (Making a Freight Calculator)

Welcome to the forum.

Thanks.  Register To Reply

12. ## Re: Building a Freight Calculator

Hello I am preparing on excel sheet and in that i need a help.
I am preparing rate sheet in that what i want is as per destination and weight cell should pick up the rate.
But must consider the weight breaks.
for example destination is ARN and weight is 100kgs that rate should be 135 like wise for all the destination.  Register To Reply

13. ## Re: Building a Freight Calculator

Hello I am preparing on excel sheet and in that i need a help.
I am preparing rate sheet in that what i want is as per destination and weight cell should pick up the rate.
But must consider the weight breaks.
for example destination is ARN and weight is 100kgs that rate should be 135 like wise for all the destination.  Register To Reply

14. ## Re: Formula Lookup? (Making a Freight Calculator)

Welcome to the forum.

Thanks.  Register To Reply