missing item find out

[majidsiddique]

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]

 

[oldbrewer]

no - my post 19 refers to the formula which tests for an error, but if it is NOT an error, OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4))
comes into play

 

[majidsiddique]

hi oldbrewer,
i still not get result. finally put the formula on column F to get item no. if you want to change any thing in sheet and 2 kindly mention. please see sheet.

sheet1

sheet2
pick item no from column G.

 

[oldbrewer]

top table row 3 of column A MD-ENSP30018 does not occur in lower table so a lookup or match statement will both return an error. How do we know you want to return MD-INT30018

 

[majidsiddique]

sheet2 column G data is correct. i want MD-INT30018X. i want to check which items are incorrect in sheet1 data. so all item shows on sheet1 column F by formula. kindly apply formula to pick all items from sheet2 column G and paste in sheet1 column F just only. same condition will apply to invoice A2392 items are different to each other sheet1 and sheet2 BUT sheet2 column G items are OK.

 

[oldbrewer]

it seems to me you want to copy column G lower table and paste in column F upper table - is that correct ?

 

[majidsiddique]

yes exactly.

 

[oldbrewer]

[TABLE="width: 938"]
<colgroup><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][/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]
[/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][/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]
[/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]
[/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]
[/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]
[/TR]
[TR]
[TD]KIP-A00104[/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][/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][/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][/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][/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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KIP-A00104[/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]
[/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][/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]
[/TR]
[TR]
[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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]in this post you seem to want KIP numbers[/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]
[/TR]
[TR]
[TD="colspan: 2"]so why not just pull columns C,D,E from the lower table ?[/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]
[/TR]
[TR]
[TD="colspan: 2"]in fact why not just use the lower table[/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]
[/TR]
[TR]
[TD="colspan: 3"]very easy to do the pulling will do it as soon as you respond with final specific needs[/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]
[/TR]
[TR]
[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]
[/TR]
</tbody>[/TABLE]

 

[majidsiddique]

the discussion has too long, so finally i did not get it your point, let we finish it but thanks to you to consider it and give your time.

 

[oldbrewer]

if the lower table is in the same order as the upper table why are you not using item and kip from the lower table - my post 27 what do you want - I would like to help you - lookups and offset with match are very simple to set up - up to you though - good luck

 

[majidsiddique]

you have seen my post 22 and i used a formula on it but did not get desired result. i asked you where is missing so just simply to modified formula and get it. why my formula is not working in post 22. you did not correction in my formula. i know your point get in column E KIP No BUT did not get Item no in column F same formula formula. i need both of it.