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.

ITEM	Merge Inv + Item	INVOICE NO	QUANTITY	Kip-01	Missing Item
MD-ENSP27530	A2370MD-ENSP27530	A2370	1	KIP-A00104
MD-ENSP30018	A2370MD-ENSP30018	A2370	1	#N/A	A2370MD-INT30018X
MD-INT30026X	A2370MD-INT30026X	A2370	1	KIP-A00045
MD-NCSP3009X	A2370MD-NCSP3009X	A2370	1	KIP-A00010
MD-SPL20020X	A2370MD-SPL20020X	A2370	1	KIP/A00198
MD-SPL25015X	A2370MD-SPL25015X	A2370	1	KIP-A00148
				KIP-A00104
MD-DRSP30018	A2392MD-DRSP30018	A2392	1	#N/A	A2392MD-DRSP25014
MD-DRSP30030	A2392MD-DRSP30030	A2392	1	#N/A	A2392MD-DRSP25018
MD-DRSP35015	A2392MD-DRSP35015	A2392	1	#N/A	A2392MD-DRSP25024
MD-DRSP35018	A2392MD-DRSP35018	A2392	1	#N/A	A2392MD-DRSP27514
MD-INT25014X	A2392MD-INT25014X	A2392	1	#N/A	A2392MD-DRSP27518
				KIP-A00104
MD-BB841000	A2453MD-BB841000	A2453	4	KIP-A00194
MD-M110706C	A2453MD-M110706C	A2453	4	#N/A	BI-300894210
MD-RCL841000	A2453MD-RCL841000	A2453	4	KIP-A00194

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet2 DATA.

Merge Inv + Item	INVOICE NO	ITEM	QUANTITY	KIP
A2370MD-ENSP27530	A2370	MD-ENSP27530	1	KIP-A00104
A2370MD-INT30018X	A2370	MD-INT30018X	1	KIP-A00075
A2370MD-INT30026X	A2370	MD-INT30026X	1	KIP-A00045
A2370MD-NCSP3009X	A2370	MD-NCSP3009X	1	KIP-A00010
A2370MD-SPL20020X	A2370	MD-SPL20020X	1	KIP/A00198
A2370MD-SPL25015X	A2370	MD-SPL25015X	1	KIP-A00148
A2392MD-DRSP25014	A2392	MD-DRSP25014	1	KIP-2073
A2392MD-DRSP25018	A2392	MD-DRSP25018	1	KIP-2073
A2392MD-DRSP25024	A2392	MD-DRSP25024	1	KIP-2073
A2392MD-DRSP27514	A2392	MD-DRSP27514	1	KIP-2086
A2392MD-DRSP27518	A2392	MD-DRSP27518	1	KIP-2073
A2453BI-300894210	A2453	BI-300894210	4	KIP-A00218
A2453MD-BB8410000	A2453	MD-BB8410000	4	KIP-A00194
A2453MD-RCL841000	A2453	MD-RCL841000	4	KIP-A00194

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

ITEM	Merge Inv + Item	INVOICE NO	QUANTITY	CORRECT ITEM	CORRECT KIP			helper
MD-ENSP27530	A2370MD-ENSP27530	A2370	1	MD-ENSP27530	KIP-A00104			1
MD-ENSP30018	A2370MD-ENSP30018	A2370	1	MD-INT30018X	KIP-A00075			2
MD-INT30026X	A2370MD-INT30026X	A2370	1	MD-INT30026X	KIP-A00045			3
MD-NCSP3009X	A2370MD-NCSP3009X	A2370	1	MD-NCSP3009X	KIP-A00010			4
MD-SPL20020X	A2370MD-SPL20020X	A2370	1	MD-SPL20020X	KIP/A00198			5
MD-SPL25015X	A2370MD-SPL25015X	A2370	1	MD-SPL25015X	KIP-A00148			6
MD-DRSP30018	A2392MD-DRSP30018	A2392	1	MD-DRSP25014	KIP-2073			7
MD-DRSP30030	A2392MD-DRSP30030	A2392	1	MD-DRSP25018	KIP-2073			8
MD-DRSP35015	A2392MD-DRSP35015	A2392	1	MD-DRSP25024	KIP-2073			9
MD-DRSP35018	A2392MD-DRSP35018	A2392	1	MD-DRSP27514	KIP-2086			10
MD-INT25014X	A2392MD-INT25014X	A2392	1	MD-DRSP27518	KIP-2073			11
MD-BB841000	A2453MD-BB841000	A2453	4	BI-300894210	KIP-A00218			12
MD-M110706C	A2453MD-M110706C	A2453	4	MD-BB8410000	KIP-A00194			13
MD-RCL841000	A2453MD-RCL841000	A2453	4	MD-RCL841000	KIP-A00194			14
Merge Inv + Item	INVOICE NO	ITEM	QUANTITY	KIP
A2370MD-ENSP27530	A2370	MD-ENSP27530	1	KIP-A00104
A2370MD-INT30018X	A2370	MD-INT30018X	1	KIP-A00075
A2370MD-INT30026X	A2370	MD-INT30026X	1	KIP-A00045
A2370MD-NCSP3009X	A2370	MD-NCSP3009X	1	KIP-A00010
A2370MD-SPL20020X	A2370	MD-SPL20020X	1	KIP/A00198
A2370MD-SPL25015X	A2370	MD-SPL25015X	1	KIP-A00148
A2392MD-DRSP25014	A2392	MD-DRSP25014	1	KIP-2073
A2392MD-DRSP25018	A2392	MD-DRSP25018	1	KIP-2073
A2392MD-DRSP25024	A2392	MD-DRSP25024	1	KIP-2073
A2392MD-DRSP27514	A2392	MD-DRSP27514	1	KIP-2086
A2392MD-DRSP27518	A2392	MD-DRSP27518	1	KIP-2073
A2453BI-300894210	A2453	BI-300894210	4	KIP-A00218
A2453MD-BB8410000	A2453	MD-BB8410000	4	KIP-A00194
A2453MD-RCL841000	A2453	MD-RCL841000	4	KIP-A00194
	formula in E2
	=OFFSET($E$19,I2,-2)
	formula in F2
	=OFFSET($E$19,I2,0)
as the lower table will be in another sheet it will be of the form =offset(sheetname!$e$19,i2,-2)

<colgroup><col><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[majidsiddique]

can you share with me your email address. my email is
majid_mug@yahoo.com
kindly send me your email, i send you email file. kindly direct apply formula on it.
Thanks.

 

[oldbrewer]

THAT IS NOT ALLOWED IN THIS FORUM - but you can put it on google sheets - anonymise it if necessary..........

 

[majidsiddique]

hi oldbrewer,
thanks a lot. i got your point, thanks again,
thanks for patience.