majidsiddique
Board Regular
- Joined
- Oct 22, 2018
- Messages
- 164
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.
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Sheet2 DATA.
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
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>