Nov 29 2020 11:35 PM
For example I have a data for calculate shipping cost
First field is port name & other is weight slab like below
Port Name | Place of Delivery | Weight Slab | 10000 | 23000 | 26000 & above | 10000 | 23000 | 26000 & above |
- | - | Container Size | 20 | 20 | 20 | 40 | 40 | 40 |
- | - | - | Rate | Rate | Rate | Rate | Rate | Rate |
PIPAVAV | KATHUWAS | - | 33500 | 43500 | 48500 | 62000 | 63250 | 63250 |
PIPAVAV | Patli | - | 34500 | 47500 | 50500 | 56250 | 63250 | 63250 |
PIPAVAV | Garhi | - | 39500 | 55500 | 62500 | 68250 | 75750 | 75750 |
PIPAVAV | ACTL | - | 43260 | 58900 | 63250 | 73200 | 81850 | 82850 |
PIPAVAV | Pyala | - | 43250 | 58900 | 63250 | 73200 | 81850 | 82850 |
PIPAVAV | Dadri | - | 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.
Dec 01 2020 05:48 AM
You can use the below formula to get the results & pass this as array (mean when you are done with formula construction press crtl + shift + enter)
{=IFERROR(INDEX(D5:I10,MATCH(TRUE,(A13&B13=A5:A10&B5:B10),0),MATCH($D$13,IF(($D$3:$I$3=$C$13),$D$2:$I$2),1)),"No Records Found")}
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.
Dec 02 2020 02:28 AM
@Faraz Shaikh thank you faraz...
my problem is solved with
=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)
CAN you help to understand below given part of this formula.
N(IF(1,{1,2}*(C2:D2>0)))),0)*C2:D2)