Matching multiple criteria from Sheet1 in Sheet2 and writing it in Sheet3

Hi All,
I need to write a macro to takes legal contract number and item sku in sheet 1, and search in Sheet 2's license information and if found write all information in sheet3 from Sheet1 and Sheet2. Otherwise if it is not found in sheet2 at all, it will only writes sheet1 information in sheet3.
Sheet3 will have all columns from SHeet1 and Sheet2 in that order.

The purpose of this exercise to validate that all products are installed per legal contract per customer.
I tried index and match however it didn't work.

Please see details below.
Any help is greatly appreciated.

Sheet 1 - Service Contract Information
• Sheet 1 contains service contract information including Contract ID, Customer Name, Legal Contract Number and Item sku number for columns A-D respectively.
• In sheet1, there might be multiple legal contracts per account
• In sheet 1, each contract might have many Item(SKU).
• There are 3400 rows in this sheet.

Sheet 2 - Product (Entitlement) Information
• Sheet 2 contains list of products installed for customer account and includes Account Number, Account Name, License Information, Product Number, Product Name (Column A-F respectively)
• In Sheet 2, column "license information" will have mixed values of legal contract number and Item Sku Numbers with also some additional text.
• There are

Logic
For each row in Sheet 1, we need to take the (legal contract number and Item SKU number) from sheet1
For each row in Sheet2, lookup (legal contract number and Item SKU number) in license information column.
○ If both are found in the cell then
§ Write all 4 columns from sheet 1 and 5 columns from Sheet2 row it into Sheet3
End if

○ Process next row in Sheet 2 until all rows are processed.

○ If no match was found in sheet2,
Write only the 4 columns from sheet 1 into sheet3
GO to next row and process the same until all rows in sheet1 are processed.

Notes:
• For each record in sheet1 we can have multiple records in sheet2. Hence we need to write our output in sheet3 and not sheet1.
• We need to keep record of all outcomes hence even if there is no match we will write the sheet1 row information in sheet3.

Sheet 1



Sheet 2


Sheet 3

Hi there... and welcome to the Excel Forum. If this is intended as a question, rather than a Tip or a Tutorial"; it's in the wrong place!! Also, you've attached a jpg, rather than a spreadsheet. That means that we have to guess what cells the data are in, and retype all this stuff before we can help. No-one is going to do that. I suggest you close this and repost in EITHER in the VBA section (if you want a VBA/macro solution) or in the General/Formulas section if you want a formula solution.

At the very least - attach a sheet. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon