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]

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]

ITEM	Merge Inv + Item	INVOICE NO	QUANTITY	Kip-01
MD-ENSP27530	A2370MD-ENSP27530	A2370	1	KIP-A00104
MD-ENSP30018	A2370MD-ENSP30018	A2370	1	#N/A
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
MD-DRSP30030	A2392MD-DRSP30030	A2392	1	#N/A
MD-DRSP35015	A2392MD-DRSP35015	A2392	1	#N/A
MD-DRSP35018	A2392MD-DRSP35018	A2392	1	#N/A
MD-INT25014X	A2392MD-INT25014X	A2392	1	#N/A
KIP-A00104
MD-BB841000	A2453MD-BB841000	A2453	4	KIP-A00194
MD-M110706C	A2453MD-M110706C	A2453	4	#N/A
MD-RCL841000	A2453MD-RCL841000	A2453	4	KIP-A00194
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
in this post you seem to want KIP numbers
so why not just pull columns C,D,E from the lower table ?
in fact why not just use the lower table
very easy to do the pulling will do it as soon as you respond with final specific needs

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

 

[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.