Help with looking up data in an Excel Database

First off, I'll upload the sample data here.

Shipping_rates.xlsx

Notice on the first tab called "Calculations" there are lines called:

Enter weight in kg:
Enter country:

as sample data, I've put "New Zealand" and "1.3kg"

Lines 6, 7 and 8 have these headings:
EMS
DHL-INTL-TRACKING
DHL-INTL-NO-TRACKING

Each of these lines is going to pull data from the three data sheets of the same names.

Columns B, C, D, E, and G are simple labels that are being pulled from one of the other sheets (namely: ISO, Continent or Zone, Base Cost, Cost/kg and Shipping ID)

Column F is the important one, which is a calculation of shipping cost based on all the other factors.

Now check the tab called "EMS".
The first country is "Argentina"
Notice that "Argentina" appears several times in the list, but each with different weights. For example, in the following weight ranges:
0 to 0.5 kg
0.5001 to 1 kg
1.0001 to 1.5 kg
1.5001 to 2 kg
2.0001 to 2.5 kg
2.5001 to 11 kg
and
11.0001 to 30 kg

So if I type "Argentina" into cell B3 and a weight like 3.51kg into cell B2, all of the cells from B6 to G8 should magically fill in for me. Get it?

Anyway, there are about 4 more "sheets" of rates to include and more data. But this, so far, is the skeleton structure of my database I'm working on.

Can someone please help with the formulas for cells B6 to G8? After I kind of get the idea, I'll be fine with finishing the rest. The idea is I don't want to go through all the work of entering the data if the formulas aren't going to work, or if I'm going at the whole project all wrong.

Thanks in advance!

Greg
LVL 1
greghollAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
The formula for D6 could be
=IFERROR(INDEX(EMS!$F$1:$F$1000,MATCH(1,INDEX((EMS!$A$1:$A$1000=$B$3)*(EMS!$D$1:$D$1000<=$B$2)*(EMS!$E$1:$E$1000>=$B$2),,),0),1),"")

Open in new window

I have set it for 1000 rows, as using entire columns will make the calculation slow.

The calculation formula for Shipping cost is Weight * Base cost * Cost/kg
=IF(AND(D6<>"",E6<>""),D6*E6*$B$2,"")
See sheet for the other formulas.

EDIT: With file
Shipping_rates.xlsx
greghollAuthor Commented:
AWESOME! LOOKING GREAT.

1) Some of the data sheets have as many as 1280 lines. Maybe I should change that to 1500 just to be sure??

2) I changed the formula for column to:
=IF(AND(D6<>"",E6<>""),D6+E6*$B$2,"")
(notice D6+E6*$B$2)
BUT...
one small thing I forgot to mention:
The number at $B$2 needs to be rounded up to the next WHOLE number.

For example, if the weight entered is 3.1kg, then the postage calculation is based on 4kg (the next whole number). Get it?

Please help with that tweak and we're done! :D
Ejgil HedegaardCommented:
Extend the ranges to what is suitable, even 10,000 is Ok, but avoid using the entire column with 1 million rows.
I have changed to 3,000 rows
Try replace Ctrl+H and change $3,000 to $300,000.
Insert a new weight, it will work, but you must wait for the calculation to finish.

The function ROUNDUP($B$2,0) does it.
Shipping_rates.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greghollAuthor Commented:
Thanks Friend. You rock! :D
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.