missing item find out

M

majidsiddique

Board Regular
Joined
Oct 22, 2018
Messages
164
hi all,
kindly help me to find out missing item below mention data.
i use formula, Vlookup(B2&"*",sheet2!A2:E45,5,0) to pick KIP No exact match item + invoice BUT formula break and give #N/A because item is not match. so i have to find out actual item from sheet2 criteria match invoice and qty then pick right item in next column sheet1. you can see missing item.


[TABLE="width: 679"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ITEM[/TD]
[TD]Merge Inv + Item[/TD]
[TD]INVOICE NO[/TD]
[TD]QUANTITY[/TD]
[TD]Kip-01[/TD]
[TD]Missing Item[/TD]
[/TR]
[TR]
[TD]MD-ENSP27530[/TD]
[TD]A2370MD-ENSP27530[/TD]
[TD]A2370[/TD]
[TD]1[/TD]
[TD]KIP-A00104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-ENSP30018[/TD]
[TD]A2370MD-ENSP30018[/TD]
[TD]A2370[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[TD]A2370MD-INT30018X[/TD]
[/TR]
[TR]
[TD]MD-INT30026X[/TD]
[TD]A2370MD-INT30026X[/TD]
[TD]A2370[/TD]
[TD]1[/TD]
[TD]KIP-A00045[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-NCSP3009X[/TD]
[TD]A2370MD-NCSP3009X[/TD]
[TD]A2370[/TD]
[TD]1[/TD]
[TD]KIP-A00010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-SPL20020X[/TD]
[TD]A2370MD-SPL20020X[/TD]
[TD]A2370[/TD]
[TD]1[/TD]
[TD]KIP/A00198[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-SPL25015X[/TD]
[TD]A2370MD-SPL25015X[/TD]
[TD]A2370[/TD]
[TD]1[/TD]
[TD]KIP-A00148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]KIP-A00104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP30018[/TD]
[TD]A2392MD-DRSP30018[/TD]
[TD]A2392[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[TD]A2392MD-DRSP25014[/TD]
[/TR]
[TR]
[TD]MD-DRSP30030[/TD]
[TD]A2392MD-DRSP30030[/TD]
[TD]A2392[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[TD]A2392MD-DRSP25018[/TD]
[/TR]
[TR]
[TD]MD-DRSP35015[/TD]
[TD]A2392MD-DRSP35015[/TD]
[TD]A2392[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[TD]A2392MD-DRSP25024[/TD]
[/TR]
[TR]
[TD]MD-DRSP35018[/TD]
[TD]A2392MD-DRSP35018[/TD]
[TD]A2392[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[TD]A2392MD-DRSP27514[/TD]
[/TR]
[TR]
[TD]MD-INT25014X[/TD]
[TD]A2392MD-INT25014X[/TD]
[TD]A2392[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[TD]A2392MD-DRSP27518[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KIP-A00104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-BB841000[/TD]
[TD]A2453MD-BB841000[/TD]
[TD]A2453[/TD]
[TD]4[/TD]
[TD]KIP-A00194[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-M110706C[/TD]
[TD]A2453MD-M110706C[/TD]
[TD]A2453[/TD]
[TD]4[/TD]
[TD]#N/A[/TD]
[TD]BI-300894210[/TD]
[/TR]
[TR]
[TD]MD-RCL841000[/TD]
[TD]A2453MD-RCL841000[/TD]
[TD]A2453[/TD]
[TD]4[/TD]
[TD]KIP-A00194[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 DATA.
[TABLE="width: 487"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Merge Inv + Item[/TD]
[TD]INVOICE NO[/TD]
[TD]ITEM[/TD]
[TD]QUANTITY[/TD]
[TD]KIP[/TD]
[/TR]
[TR]
[TD]A2370MD-ENSP27530[/TD]
[TD]A2370[/TD]
[TD]MD-ENSP27530[/TD]
[TD]1[/TD]
[TD]KIP-A00104[/TD]
[/TR]
[TR]
[TD]A2370MD-INT30018X[/TD]
[TD]A2370[/TD]
[TD]MD-INT30018X[/TD]
[TD]1[/TD]
[TD]KIP-A00075[/TD]
[/TR]
[TR]
[TD]A2370MD-INT30026X[/TD]
[TD]A2370[/TD]
[TD]MD-INT30026X[/TD]
[TD]1[/TD]
[TD]KIP-A00045[/TD]
[/TR]
[TR]
[TD]A2370MD-NCSP3009X[/TD]
[TD]A2370[/TD]
[TD]MD-NCSP3009X[/TD]
[TD]1[/TD]
[TD]KIP-A00010[/TD]
[/TR]
[TR]
[TD]A2370MD-SPL20020X[/TD]
[TD]A2370[/TD]
[TD]MD-SPL20020X[/TD]
[TD]1[/TD]
[TD]KIP/A00198[/TD]
[/TR]
[TR]
[TD]A2370MD-SPL25015X[/TD]
[TD]A2370[/TD]
[TD]MD-SPL25015X[/TD]
[TD]1[/TD]
[TD]KIP-A00148[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25014[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25014[/TD]
[TD]1[/TD]
[TD]KIP-2073[/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25018[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25018[/TD]
[TD]1[/TD]
[TD]KIP-2073[/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25024[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25024[/TD]
[TD]1[/TD]
[TD]KIP-2073[/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP27514[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP27514[/TD]
[TD]1[/TD]
[TD]KIP-2086[/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP27518[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP27518[/TD]
[TD]1[/TD]
[TD]KIP-2073[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2453BI-300894210[/TD]
[TD]A2453[/TD]
[TD]BI-300894210[/TD]
[TD]4[/TD]
[TD]KIP-A00218[/TD]
[/TR]
[TR]
[TD]A2453MD-BB8410000[/TD]
[TD]A2453[/TD]
[TD]MD-BB8410000[/TD]
[TD]4[/TD]
[TD]KIP-A00194[/TD]
[/TR]
[TR]
[TD]A2453MD-RCL841000[/TD]
[TD]A2453[/TD]
[TD]MD-RCL841000[/TD]
[TD]4[/TD]
[TD]KIP-A00194[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
the offset function works like this

go down so many cells, then go across (left or right) so many cells

so I was going down in G column, then LEFT BY 2 COLUMNS, HENCE -2
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
HI,
in the same, i used -3 to pick item but result is not ok, why? it pick mix KIP and item.
[TABLE="width: 115"]
<colgroup><col></colgroup><tbody>[TR]
[TD]KIP-A00104[/TD]
[/TR]
[TR]
[TD]MD-INT30018X[/TD]
[/TR]
[TR]
[TD]KIP-A00045[/TD]
[/TR]
[TR]
[TD]KIP-A00010[/TD]
[/TR]
[TR]
[TD]KIP/A00198[/TD]
[/TR]
[TR]
[TD]KIP-A00148[/TD]
[/TR]
[TR]
[TD]MD-DRSP25014[/TD]
[/TR]
[TR]
[TD]MD-DRSP25018[/TD]
[/TR]
[TR]
[TD]MD-DRSP25024[/TD]
[/TR]
[TR]
[TD]MD-DRSP27514[/TD]
[/TR]
[TR]
[TD]MD-RINT30018X[/TD]
[/TR]
[TR]
[TD]BI-300894210[/TD]
[/TR]
[TR]
[TD]MD-BB8410000[/TD]
[/TR]
[TR]
[TD]KIP-A00194[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Boss,
i use this, sheet2 column G and H and use formula sheet1, result is not OK
=IF(ISERROR(OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4)),OFFSET(Sheet2!$H$1,MATCH(G2,Sheet2!$H$2:$H$15,0),-1),OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4))

and same formula work OK, sheet column F numbers 23 to 36 -1 pick kip no
=IF(ISERROR(OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4)),OFFSET(Sheet2!$F$1,MATCH(G2,Sheet2!$F$2:$F$15,0),-3),OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4))

same criteria just change sheet2 column H repeat numbers.


[TABLE="width: 170"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]MD-ENSP27530[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]MD-INT30018X[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]MD-INT30026X[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]MD-NCSP3009X[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]MD-SPL20020X[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]MD-SPL25015X[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]MD-DRSP25014[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]MD-DRSP25018[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]MD-DRSP25024[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]MD-DRSP27514[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]MD-RINT30018X[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]BI-300894210[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]MD-BB8410000[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]MD-RCL841000[/TD]
[TD="align: right"]36[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 1009"]
<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]ITEM[/TD]
[TD]Merge Inv + Item[/TD]
[TD]INVOICE NO[/TD]
[TD]QUANTITY[/TD]
[TD]Kip-01[/TD]
[TD]Missing Item[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-ENSP27530[/TD]
[TD]A2370MD-ENSP27530[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-ENSP30018[/TD]
[TD]A2370MD-ENSP30018[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD]A2370MD-INT30018X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-INT30026X[/TD]
[TD]A2370MD-INT30026X[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00045[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-NCSP3009X[/TD]
[TD]A2370MD-NCSP3009X[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-SPL20020X[/TD]
[TD]A2370MD-SPL20020X[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]KIP/A00198[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-SPL25015X[/TD]
[TD]A2370MD-SPL25015X[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00148[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KIP-A00104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP30018[/TD]
[TD]A2392MD-DRSP30018[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD]A2392MD-DRSP25014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP30030[/TD]
[TD]A2392MD-DRSP30030[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD]A2392MD-DRSP25018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP35015[/TD]
[TD]A2392MD-DRSP35015[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD]A2392MD-DRSP25024[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP35018[/TD]
[TD]A2392MD-DRSP35018[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD]A2392MD-DRSP27514[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-INT25014X[/TD]
[TD]A2392MD-INT25014X[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD]A2392MD-DRSP27518[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KIP-A00104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-BB841000[/TD]
[TD]A2453MD-BB841000[/TD]
[TD]A2453[/TD]
[TD="align: right"]4[/TD]
[TD]KIP-A00194[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-M110706C[/TD]
[TD]A2453MD-M110706C[/TD]
[TD]A2453[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]#N/A[/TD]
[TD]BI-300894210[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-RCL841000[/TD]
[TD]A2453MD-RCL841000[/TD]
[TD]A2453[/TD]
[TD="align: right"]4[/TD]
[TD]KIP-A00194[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2 DATA.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Merge Inv + Item[/TD]
[TD]INVOICE NO[/TD]
[TD]ITEM[/TD]
[TD]QUANTITY[/TD]
[TD]KIP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-ENSP27530[/TD]
[TD]A2370[/TD]
[TD]MD-ENSP27530[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-INT30018X[/TD]
[TD]A2370[/TD]
[TD]MD-INT30018X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00075[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-INT30026X[/TD]
[TD]A2370[/TD]
[TD]MD-INT30026X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00045[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-NCSP3009X[/TD]
[TD]A2370[/TD]
[TD]MD-NCSP3009X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-SPL20020X[/TD]
[TD]A2370[/TD]
[TD]MD-SPL20020X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP/A00198[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-SPL25015X[/TD]
[TD]A2370[/TD]
[TD]MD-SPL25015X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00148[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25014[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25014[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25018[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25018[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25024[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25024[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP27514[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP27514[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2086[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP27518[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP27518[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2453BI-300894210[/TD]
[TD]A2453[/TD]
[TD]BI-300894210[/TD]
[TD="align: right"]4[/TD]
[TD]KIP-A00218[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2453MD-BB8410000[/TD]
[TD]A2453[/TD]
[TD]MD-BB8410000[/TD]
[TD="align: right"]4[/TD]
[TD]KIP-A00194[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2453MD-RCL841000[/TD]
[TD]A2453[/TD]
[TD]MD-RCL841000[/TD]
[TD="align: right"]4[/TD]
[TD]KIP-A00194[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]lets start again - because you have MD-ENSP30018 in A3 you get the #na - what is the rule that says[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]MD-INT30018 is the correct or acceptible match ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 881"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ITEM[/TD]
[TD]Merge Inv + Item[/TD]
[TD]INVOICE NO[/TD]
[TD]QUANTITY[/TD]
[TD]Kip-01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-ENSP27530[/TD]
[TD]A2370MD-ENSP27530[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-ENSP30018[/TD]
[TD]A2370MD-ENSP30018[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-INT30026X[/TD]
[TD]A2370MD-INT30026X[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-NCSP3009X[/TD]
[TD]A2370MD-NCSP3009X[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-SPL20020X[/TD]
[TD]A2370MD-SPL20020X[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-SPL25015X[/TD]
[TD]A2370MD-SPL25015X[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KIP-A00104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP30018[/TD]
[TD]A2392MD-DRSP30018[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP30030[/TD]
[TD]A2392MD-DRSP30030[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP35015[/TD]
[TD]A2392MD-DRSP35015[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP35018[/TD]
[TD]A2392MD-DRSP35018[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-INT25014X[/TD]
[TD]A2392MD-INT25014X[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KIP-A00104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-BB841000[/TD]
[TD]A2453MD-BB841000[/TD]
[TD]A2453[/TD]
[TD="align: right"]4[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-M110706C[/TD]
[TD]A2453MD-M110706C[/TD]
[TD]A2453[/TD]
[TD="align: right"]4[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-RCL841000[/TD]
[TD]A2453MD-RCL841000[/TD]
[TD]A2453[/TD]
[TD="align: right"]4[/TD]
[TD]no match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2 DATA.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Merge Inv + Item[/TD]
[TD]INVOICE NO[/TD]
[TD]ITEM[/TD]
[TD]QUANTITY[/TD]
[TD]KIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-ENSP27530[/TD]
[TD]A2370[/TD]
[TD]MD-ENSP27530[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-INT30018X[/TD]
[TD]A2370[/TD]
[TD]MD-INT30018X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00075[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-INT30026X[/TD]
[TD]A2370[/TD]
[TD]MD-INT30026X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00045[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-NCSP3009X[/TD]
[TD]A2370[/TD]
[TD]MD-NCSP3009X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-SPL20020X[/TD]
[TD]A2370[/TD]
[TD]MD-SPL20020X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP/A00198[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2370MD-SPL25015X[/TD]
[TD]A2370[/TD]
[TD]MD-SPL25015X[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25014[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25014[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25018[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25018[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP25024[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP25024[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP27514[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP27514[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2086[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2392MD-DRSP27518[/TD]
[TD]A2392[/TD]
[TD]MD-DRSP27518[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2453BI-300894210[/TD]
[TD]A2453[/TD]
[TD]BI-300894210[/TD]
[TD="align: right"]4[/TD]
[TD]KIP-A00218[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2453MD-BB8410000[/TD]
[TD]A2453[/TD]
[TD]MD-BB8410000[/TD]
[TD="align: right"]4[/TD]
[TD]KIP-A00194[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2453MD-RCL841000[/TD]
[TD]A2453[/TD]
[TD]MD-RCL841000[/TD]
[TD="align: right"]4[/TD]
[TD]KIP-A00194[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]row 15 A2453MD-BB841000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]row 33 A2453MD-BB8410000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]is this a typo ???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
please see the image sheet1.

29bce8e19ce43408cb5e293263df3ce4-full.png


sheet2

61ff5d172ff70c0b9b6a7a46ea71730f-full.png


i use this formula to get item not KIP.
=IF(ISERROR(OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4)),OFFSET(Sheet2!$H$1,MATCH(G2,Sheet2!$H$2:$H$15,0),-1),OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4))
result is not OK.
Thanks to reply
 
Upvote 0
=IF(ISERROR(OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4)
the 4 at the end is finding KIP in lower table

if an error you are getting G2 in lower table
if not an error you are getting the KIP in col E lower table


 
Upvote 0
Hi oldbrewer,
if it is not an error, so we cannot pick item no in column F sheet1? is there any change in formula to pick item based on given criteria?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top