Vlookup with two lookup value (One is exact & other is Approximate)

Brass Contributor

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.

2 Replies

Hi @devyadav2008 

 

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")}

 

Snag_e1a90b.png

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.

@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)