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]

in your bottom table add a helper column that shows 1 if a match with the top table and 0 if no match, then in another helper put invoice number&0 ie it will show A23700

then search bottom table for A23700

 

[majidsiddique]

Hi oldbrewer,
can you do this? like a sample.

 

[oldbrewer]

just find A23700 in helper column with offset - say helper is in column K from K3 down

=offset($k$2,match("A23700",$k$4:$k$100,0),-6)

this gives you whatever is in column C

 

[majidsiddique]

hi oldbrewer,
kindly explain more, helper column values. i want find sheet2 and paste it sheet1 match invoice no A2370 sheet1.

 

[oldbrewer]

your 5th column in the top table uses a formula to pull the kip number. Where this formula brings back #na you need to pull the kip number in a different way. The helper1 column sees if rows in the lower table match the top table and if no match returns a 0 - then helper2 wherever there is a 0 in helper 1 returns the invoice number - then helper3 where helper2 is not blank returns the kip number

so your formula needs to be .......=if(iserror(yourformula)),go find the kip number in helper3, your formula)

use offset(match) to match the invoice number to helper2 column offsetting one column to the right ie to helper3

 

[majidsiddique]

hi oldbrewer,
please do it on a sheet. i am doing but not get result. please do it on excel sheet. where value put and how to apply formula with helper?

 

[oldbrewer]

[TABLE="width: 1173"]
<colgroup><col><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][/TD]
[TD][/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]
[TD][/TD]
[/TR]
[TR]
[TD]MD-ENSP30018[/TD]
[TD]A2370MD-ENSP30018[/TD]
[TD]A2370[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-A00075[/TD]
[TD]correct[/TD]
[TD][/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]
[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]
[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]
[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]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP30018[/TD]
[TD]A2392MD-DRSP30018[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD="colspan: 4"]where there are consecutive errors my approach fails[/TD]
[/TR]
[TR]
[TD]MD-DRSP30030[/TD]
[TD]A2392MD-DRSP30030[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD]I will thik on….[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP35015[/TD]
[TD]A2392MD-DRSP35015[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP35018[/TD]
[TD]A2392MD-DRSP35018[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD="colspan: 2"]col % top table now formula driven[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-INT25014X[/TD]
[TD]A2392MD-INT25014X[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KIP-A00104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#N/A[/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-A00218[/TD]
[TD][/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]KIP-A00218[/TD]
[TD][/TD]
[TD][/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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](if not return inv num)[/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]
[TD][/TD]
[/TR]
[TR]
[TD]Merge Inv + Item[/TD]
[TD]INVOICE NO[/TD]
[TD]ITEM[/TD]
[TD]QUANTITY[/TD]
[TD]KIP[/TD]
[TD]does col A match to col B table 1[/TD]
[TD]if inv num ret kip num[/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]
[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]A2370[/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]
[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]
[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]
[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]
[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]A2392[/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]A2392[/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]A2392[/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]A2392[/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]A2392[/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]A2453[/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]A2453[/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]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 1173"]
<colgroup><col><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]target row num[/TD]
[TD][/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]23[/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]KIP-A00075[/TD]
[TD]24[/TD]
[TD][/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]25[/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]26[/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]27[/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]28[/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]KIP-2073[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP30030[/TD]
[TD]A2392MD-DRSP30030[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP35015[/TD]
[TD]A2392MD-DRSP35015[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-DRSP35018[/TD]
[TD]A2392MD-DRSP35018[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2086[/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MD-INT25014X[/TD]
[TD]A2392MD-INT25014X[/TD]
[TD]A2392[/TD]
[TD="align: right"]1[/TD]
[TD]KIP-2073[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KIP-A00104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KIP-A00218[/TD]
[TD]34[/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]35[/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]KIP-A00194[/TD]
[TD]36[/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]col E[/TD]
[TD][/TD]
[TD]col G[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2 DATA.[/TD]
[TD][/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]row num[/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="align: right"]23[/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="align: right"]24[/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="align: right"]25[/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="align: right"]26[/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="align: right"]27[/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="align: right"]28[/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="align: right"]29[/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="align: right"]30[/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="align: right"]31[/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="align: right"]32[/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="align: right"]33[/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="align: right"]34[/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="align: right"]35[/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="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]formula in cell E2[/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]=IF(ISERROR(OFFSET($A$22,MATCH(B2,$A$23:$A$36,0),4)),OFFSET($G$22,MATCH(F2,$G$23:$G$36,0),-2),OFFSET($A$22,MATCH(B2,$A$23:$A$36,0),4))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]this is a much simpler approach requiring only one helper cell in col G of lower table[/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]check accuracy please[/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[majidsiddique]

Hi Oldbrewer,
its really a good and fantastic approach, But tell me one thing, whatabout -2 show column no where to to item pick or anything else?

=IF(ISERROR(OFFSET($A$22,MATCH(B2,$A$23:$A$36,0),4)),OFFSET($G$22,MATCH(F2,$G$23:$G$36,0),-2),OFFSET($A$22,MATCH(B2,$A$23:$A$36,0),4))
OFFSET($G$22,MATCH(F2,$G$23:$G$36,0),-2)

i use this,
=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),-1),OFFSET(Sheet2!$A$1,MATCH(B2,Sheet2!$A$2:$A$15,0),4))
anwer if OK,

BUT i use this, answer 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))
can you tell me where is mistake.
first formula pick KIP No OK.

and second i want to pick item no. see the result.
sheet 2 H2 same copy 23 to 36 no and before one column G copy and paste item no.

[TABLE="width: 111"]
<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]