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

%3CLINGO-SUB%20id%3D%22lingo-sub-1939466%22%20slang%3D%22en-US%22%3EVlookup%20with%20two%20lookup%20value%20(One%20is%20exact%20%26amp%3B%20other%20is%20Approximate)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1939466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EFor%20example%20I%20have%20a%20data%20for%20calculate%20shipping%20cost%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EFirst%20field%20is%20port%20name%20%26amp%3B%20other%20is%20weight%20slab%20like%20below%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22bbTable%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EPort%20Name%3C%2FTD%3E%3CTD%3EPlace%20of%20Delivery%3C%2FTD%3E%3CTD%3EWeight%20Slab%3C%2FTD%3E%3CTD%3E10000%3C%2FTD%3E%3CTD%3E23000%3C%2FTD%3E%3CTD%3E26000%20%26amp%3B%20above%3C%2FTD%3E%3CTD%3E10000%3C%2FTD%3E%3CTD%3E23000%3C%2FTD%3E%3CTD%3E26000%20%26amp%3B%20above%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3EContainer%20Size%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3ERate%3C%2FTD%3E%3CTD%3ERate%3C%2FTD%3E%3CTD%3ERate%3C%2FTD%3E%3CTD%3ERate%3C%2FTD%3E%3CTD%3ERate%3C%2FTD%3E%3CTD%3ERate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPIPAVAV%3C%2FTD%3E%3CTD%3EKATHUWAS%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E%3CDIV%3E33500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E43500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E48500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E62000%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E63250%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E63250%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPIPAVAV%3C%2FTD%3E%3CTD%3EPatli%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E%3CDIV%3E34500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E47500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E50500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E56250%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E63250%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E63250%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPIPAVAV%3C%2FTD%3E%3CTD%3EGarhi%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E%3CDIV%3E39500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E55500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E62500%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E68250%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E75750%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E75750%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPIPAVAV%3C%2FTD%3E%3CTD%3EACTL%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E%3CDIV%3E43260%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E58900%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E63250%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E73200%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E81850%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E82850%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPIPAVAV%3C%2FTD%3E%3CTD%3EPyala%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E%3CDIV%3E43250%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E58900%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E63250%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E73200%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E81850%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E82850%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPIPAVAV%3C%2FTD%3E%3CTD%3EDadri%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3CTD%3E%3CDIV%3E39170%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E52670%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E55870%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E64022%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E71962%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E71962%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20to%20pick%20rate%20of%20port%20wise%2C%20place%20of%20delivery%20wise%2C%20weight%20wise%20%26amp%3B%20container%20size%20wise%20%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Efor%20example%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EPipavav%20Khatuwas%2020%20feet%20container%20with%2010027%20KG.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1939466%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1944442%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20two%20lookup%20value%20(One%20is%20exact%20%26amp%3B%20other%20is%20Approximate)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1944442%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F567527%22%20target%3D%22_blank%22%3E%40devyadav2008%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20below%20formula%20to%20get%20the%20results%20%26amp%3B%20pass%20this%20as%20array%20(mean%20when%20you%20are%20done%20with%20formula%20construction%20press%20crtl%20%2B%20shift%20%2B%20enter)%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7B%3DIFERROR(INDEX(D5%3AI10%2CMATCH(TRUE%2C(A13%26amp%3BB13%3DA5%3AA10%26amp%3BB5%3AB10)%2C0)%2CMATCH(%24D%2413%2CIF((%24D%243%3A%24I%243%3D%24C%2413)%2C%24D%242%3A%24I%242)%2C1))%2C%22No%20Records%20Found%22)%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_e1a90b.png%22%20style%3D%22width%3A%20643px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236902iC7BBBAAF06D84B66%2Fimage-dimensions%2F643x246%3Fv%3D1.0%22%20width%3D%22643%22%20height%3D%22246%22%20role%3D%22button%22%20title%3D%22Snag_e1a90b.png%22%20alt%3D%22Snag_e1a90b.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20%26amp%3B%20like%20it%20to%20help%20the%20other%20members%20find%20it%20more.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948012%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20two%20lookup%20value%20(One%20is%20exact%20%26amp%3B%20other%20is%20Approximate)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3Bthank%20you%20faraz...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20problem%20is%20solved%20with%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUM(IFERROR(INDEX(('Rate%20Source%20File'!D%244%3AF%249%2C'Rate%20Source%20File'!G%244%3AI%249)%2CMATCH(1%2C('Rate%20Source%20File'!A%244%3AA%249%3DA2)*('Rate%20Source%20File'!B%244%3AB%249%3DB2)%2C0)%2CMATCH(E2%2C%7B0%2C10000%2C23000%7D)%2CN(IF(1%2C%7B1%2C2%7D*(C2%3AD2%26gt%3B0))))%2C0)*C2%3AD2)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECAN%20you%20help%20to%20understand%20below%20given%20part%20of%20this%20formula.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EN(IF(1%2C%7B1%2C2%7D*(C2%3AD2%26gt%3B0))))%2C0)*C2%3AD2)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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)