How do I use the vlookup function with merged cells?

Hi All,

Iam a newbie and not sure if this is the right sub-forum to ask this query.

I have a file with a sheet "Master Consumption", here the in the 2nd column I have the item code of a product, with several rows merged together, in the following column it shows the parts used to make up the product with details of those parts (like shape, size, color, unit prc, qty used and total prc etc..) in various rows. This sheet has all the products I have in the same manner.

The 2nd sheet of the file is where I try to generate the Invoice, what I want to do is when I type the item code, the details of the product must show up in the following columns, and it does (only for the merged cells), but in the following columns where the rows are not merged, it shows result for only the 1st row, but N/A for the rest of the rows. And I have no idea what to do.

I have the file uploaded for your review. Inventory - Invoice.xlsx

I will appreciate if someone can help me here, while I browse thru the forum for answers.

Look forward to a favorable reply to my query.

Tks/Kevin.