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.

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
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

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
Avatar of gregholl

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
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