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]

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

 

[majidsiddique]

HI,
in the same, i used -3 to pick item but result is not ok, why? it pick mix KIP and item.

KIP-A00104

MD-INT30018X

KIP-A00045

KIP-A00010

KIP/A00198

KIP-A00148

MD-DRSP25014

MD-DRSP25018

MD-DRSP25024

MD-DRSP27514

MD-RINT30018X

BI-300894210

MD-BB8410000

KIP-A00194

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

 

[oldbrewer]

i RUN DOWN COL g TO MATCH THE ROW NUMBER THEN MOVE 2 TO THE LEFT TO GET THE KIP NUMBER

 

[majidsiddique]

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.

MD-ENSP27530	23
MD-INT30018X	24
MD-INT30026X	25
MD-NCSP3009X	26
MD-SPL20020X	27
MD-SPL25015X	28
MD-DRSP25014	29
MD-DRSP25018	30
MD-DRSP25024	31
MD-DRSP27514	32
MD-RINT30018X	33
BI-300894210	34
MD-BB8410000	35
MD-RCL841000	36

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

 

[oldbrewer]

is the 23 in row 1 or row 2

 

[oldbrewer]

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
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
lets start again - because you have MD-ENSP30018 in A3 you get the #na - what is the rule that says
MD-INT30018 is the correct or acceptible match ?

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

 

[oldbrewer]

ITEM	Merge Inv + Item	INVOICE NO	QUANTITY	Kip-01
MD-ENSP27530	A2370MD-ENSP27530	A2370	1	match
MD-ENSP30018	A2370MD-ENSP30018	A2370	1	no match
MD-INT30026X	A2370MD-INT30026X	A2370	1	match
MD-NCSP3009X	A2370MD-NCSP3009X	A2370	1	match
MD-SPL20020X	A2370MD-SPL20020X	A2370	1	match
MD-SPL25015X	A2370MD-SPL25015X	A2370	1	match
KIP-A00104				no match
MD-DRSP30018	A2392MD-DRSP30018	A2392	1	no match
MD-DRSP30030	A2392MD-DRSP30030	A2392	1	no match
MD-DRSP35015	A2392MD-DRSP35015	A2392	1	no match
MD-DRSP35018	A2392MD-DRSP35018	A2392	1	no match
MD-INT25014X	A2392MD-INT25014X	A2392	1	no match
KIP-A00104				no match
MD-BB841000	A2453MD-BB841000	A2453	4	no match
MD-M110706C	A2453MD-M110706C	A2453	4	no match
MD-RCL841000	A2453MD-RCL841000	A2453	4	no match
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
row 15 A2453MD-BB841000
row 33 A2453MD-BB8410000
is this a typo ???

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

 

[majidsiddique]

please see the image sheet1.

sheet2

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

 

[oldbrewer]

=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

 

[majidsiddique]

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?