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

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.

2 Replies

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

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.

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

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