Link to home
Start Free TrialLog in
Avatar of gregholl
greghollFlag for Ecuador

asked on

Help with looking up data in an Excel Database

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


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:

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
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!

Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

The formula for D6 could be

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
See sheet for the other formulas.

EDIT: With file
Avatar of gregholl



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:
(notice D6+E6*$B$2)
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
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Friend. You rock! :D