• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

two lookup value vlookup

For example I have a data for calculate shipping cost
First field is port name & other is weight slab like below
Port NamePlace of DeliveryWeight Slab100002300026000 & above100002300026000 & above
--Container Size202020404040
---RateRateRateRateRateRate
PIPAVAVKATHUWAS-
33500​
43500​
48500​
62000​
63250​
63250​
PIPAVAVPatli-
34500​
47500​
50500​
56250​
63250​
63250​
PIPAVAVGarhi-
39500​
55500​
62500​
68250​
75750​
75750​
PIPAVAVACTL-
43260​
58900​
63250​
73200​
81850​
82850​
PIPAVAVPyala-
43250​
58900​
63250​
73200​
81850​
82850​
PIPAVAVDadri-
39170​
52670​
55870​
64022​
71962​
71962​


I have to pick rate of port wise, place of delivery wise, weight wise & container size wise :
for example
Pipavav Khatuwas 20 feet container with 10027 KG.
 
thank you so much for your reply .. but i have container nos with heading of 20 & 40 like given below

Port NamePlace of DeliveryContainer (20'')Container (40'')Gross Weight
PIPAVAVKATHUWAS
0​
2​
20518​
PIPAVAVKATHUWAS
0​
1​
8694​
PIPAVAVKATHUWAS
0​
4​
14080​
PIPAVAVKATHUWAS
1​
0​
3069​
PIPAVAVKATHUWAS
1​
0​
994​
PIPAVAVKATHUWAS
0​
2​
37087​
 

Peter Bartholomew

Well-Known Member
You certainly believe in making life difficult! I would switch to
Code:
= INDEX( rateTbl,
  MATCH(Port&Delivery, portHdr&deliveryHdr,0),
  3*(Size="Container (40"")") + MATCH(Weight, weightHdr) )
or
Code:
= LET(
  containerRate, SWITCH(@Size,size20,rate20,size40,rate40),
  weightRate, XLOOKUP(@Weight, weightHdr, containerRate,,-1),
  FILTER(weightRate, (portHdr=@Port)*(deliveryHdr=@Delivery)) )
where 'size20' and 'size40' are names given to your text descriptions.

Hopefully someone else will come up with a solution using non-365 and traditional direct cell references.
 

bosco_yip

Excel Ninja
Try........

In "Data Required" sheet G2 array CSE formula copied down :

=SUM(IFERROR(INDEX(('Rate Source File'!D$4:F$9,'Rate Source File'!G$4:I$9),MATCH(1,('Rate Source File'!A$4:A$9=A2)*('Rate Source File'!B$4:B$9=B2),0),MATCH(E2,{0,10000,23000}),N(IF(1,{1,2}*(C2:D2>0)))),0)*C2:D2)

72263
 

Attachments

Try........

In "Data Required" sheet G2 array CSE formula copied down :

=SUM(IFERROR(INDEX(('Rate Source File'!D$4:F$9,'Rate Source File'!G$4:I$9),MATCH(1,('Rate Source File'!A$4:A$9=A2)*('Rate Source File'!B$4:B$9=B2),0),MATCH(E2,{0,10000,23000}),N(IF(1,{1,2}*(C2:D2>0)))),0)*C2:D2)

View attachment 72263

Hi.. Bosco

Thank you so much for share such a great formula and this is working also after some alteration in formula as per my need.

Altered Formula :

=SUM(IFERROR(INDEX(('IHC-Contract'!$E$4:$I$21,'IHC-Contract'!$J$4:$N$21),MATCH(1,('IHC-Contract'!$A$4:$A$21=Tentative!C423)*('IHC-Contract'!$B$4:$B$21=Tentative!E423)*('IHC-Contract'!$C$4:$C$21=Tentative!F423),0),MATCH(Tentative!I423,{0,10000,20000,23000,26000}),N(IF(1,{1,2}*(Tentative!G423:H423>0)))),0)*Tentative!G423:H423)

I have two point,

1. for some criteria have to add in this formula or in shared by you

Container (20'')Container (40'')Gross Weight
1​
2​
12310​
0​
3​
42638​
1​
3​
77700​

1 case - 20" container -1 & 40" container-2, total nos of container is 3
then the charging weight should be equal to 12310/3=4103 instead of total weight 12310

the charging weight should be similar all cases as 1 case like

chargeable weight in 2 case = 42638/3
chargeable weight in 3 case = 77700/4

So please alter formula as per this criteria also.

And

the main thing that i am not understand the formula part is

N(IF(1,{1,2}*(C2:D2>0)))),0)*C2:D2)

so please share knowledge about it also so that i can also understand this.
 

bosco_yip

Excel Ninja
..............
1 case - 20" container -1 & 40" container-2, total nos of container is 3
then the charging weight should be equal to 12310/3=4103 instead of total weight 12310 .............
Extract from your statement :

20' container x 1no + 40' container x 2nos =3nos
Weight of each container =12310/
3 =4103

Then,

Weight of "1no x 20'container" is equal to "1no x 40'container" >> ???? something wrong !

Please give explanation of this
 
Extract from your statement :

20' container x 1no + 40' container x 2nos =3nos
Weight of each container =12310/
3 =4103

Then,

Weight of "1no x 20'container" is equal to "1no x 40'container" >> ???? something wrong !

Please give explanation of this

Hi Bosco..

We have rate according to slab of weight for example:

For 40" container
Weight : 10000 20000 23000 26000 26000 & above
Rate 100 200 300 400 500

Now if we have 2 container of 40" with weight of 22000, then the rate will be apply as 200 (22000/2=11000)
 
Top