Refer to a cell in a table imported fromExcel

[CJ_London]

if it is a text field the the sorting order changes which matters when using <= or >=. As numbers the order would be

1
2
11
12

as text the orders would be

1
11
12
2

to fix you would need to restate your values as

01
02
11
12

and if your largest number was 3 digits

001
002
011
012

 

[MarkK]

Hey Bob,
To implement the solution I proposed you need to do a numeric sort on the Weight field, so making it a string doesn't make sense. You can see from what CJ is saying that a numeric sort on a string adds cost without payoff.

 

[Bobp3114]

Hey Bob,
To implement the solution I proposed you need to do a numeric sort on the Weight field, so making it a string doesn't make sense. You can see from what CJ is saying that a numeric sort on a string adds cost without payoff.

Hi Mark
Maybe I did not explain clearly...your solution works fine for that table....I have another table...that requires a similar solution
tblESSRates tblESSRates
I was trying to change the KG field of the previous example to suit the OriginCountry field here...any thoughts would be appreciated
Bob

EntryID	OriginCountry	DestHongKongChina	DestAustraliaNewZealand	DestRestOfAsia	DestEurope	DestAmericas	DestRestOfWorld
1​	China	0.90​	1.62​	0.90​	0.90​	0.90​	0.90​
2​	Hong Kong	0.90​	1.65​	0.90​	0.90​	0.90​	0.90​
3​	Rest Of Asia	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​
4​	Europe	0.20​	0.20​	0.20​	0.20​	0.20​	0.20​
5​	Americas	0.20​	0.20​	0.20​	0.20​	0.20​	0.20​
6​	Rest Of World	0.20​	0.20​	0.20​	0.20​	0.20​	0.20​

 

[MarkK]

Bob, a couple of problems,
1) This data is not normalized. Remember how @MajP fixed your previous spreadsheet? That job needs to be done for this spreadsheet.
2) This is not a series where price varies with weight. Here you have discrete pairs, each with a fixed price.
a) To get a result here you will have to supply different parameters (origin, not weight), and
b) the logic that constitutes a match will differ (weight will not fall in a range, origin will require an exact match)
Hope this helps,

 

[MajP]

Origin	Destination	Rate
China	Hong Kong / China	0.90
China	Australia /New Zealand	1.62
China	Rest of Asia	0.90
China	Europe	0.90
China	Americas	0.90
China	Rest of World	0.90
Hong Kong	Hong Kong / China	0.90
Hong Kong	Australia /New Zealand	1.65
Hong Kong	Rest of Asia	0.90
Hong Kong	Europe	0.90
Hong Kong	Americas	0.90
Hong Kong	Rest of World	0.90
Rest Of Asia	Hong Kong / China	0.00
Rest Of Asia	Australia /New Zealand	0.00
Rest Of Asia	Rest of Asia	0.00
Rest Of Asia	Europe	0.00
Rest Of Asia	Americas	0.00
Rest Of Asia	Rest of World	0.00
Europe	Hong Kong / China	0.20
Europe	Australia /New Zealand	0.20
Europe	Rest of Asia	0.20
Europe	Europe	0.20
Europe	Americas	0.20
Europe	Rest of World	0.20
Americas	Hong Kong / China	0.20
Americas	Australia /New Zealand	0.20
Americas	Rest of Asia	0.20
Americas	Europe	0.20
Americas	Americas	0.20
Americas	Rest of World	0.20
Rest Of World	Hong Kong / China	0.20
Rest Of World	Australia /New Zealand	0.20
Rest Of World	Rest of Asia	0.20
Rest Of World	Europe	0.20
Rest Of World	Americas	0.20
Rest Of World	Rest of World	0

Public Function GetShippingRate(Origin as string, Destination as string) as currency
  GetShippingRate = dlookup("Rate","NormalizedTable", "Origin = '" & Origin & "' AND Destination = '" & destination & "'")
end function