I have two sheets, Sheet 1 which has all the entries of Part Name in the system and specialized data of that part like sequence, due date and quality.

Sheet 2 consists of Part Name which is common in both the sheets and all the other columns differ from sheet 1 except part name, the sheet 2 columns are issue, error, quality, supervisor, line No.

Sheet 1:

I have two sheets, Sheet 1 which has all the entries of Part Name in the system and specialized data of that part like sequence, due date and quality.

Sheet 2 consists of Part Name which is common in both the sheets and all the other columns differ from sheet 1 except part name, the sheet 2 columns are issue, error, quality, supervisor, line No.

Sheet 1:

Sheet 2:

n both sheets, part names are spread in a random order.

I want to build a program so that the part name in sheet 2 gets the data from sheet 1 against their specific part number and I get a data set which consist of same amount of part numbers in sheet 2 but with the entries of sheet 1 column like sequence, due date, and quality.

Also there could be a case where parts from sheet one are not there in sheet 2 and vice versa

My final output should consist of sheet 2 columns entries like Part Number, Issue, error, quality, supervisor, Line No, Sequence, Due Date, Quality all in a single sheet for a specific number of parts mentioned in sheet 2.

Final output that I want:

This is a sample data of the problem I am facing with, My actual data consist of thousands of entries in part number and hundreds of particulars for that part number

• excel
• vba

2 Answers 2

You just need XLOOKUP() in Sheet2 like-

=XLOOKUP(A2,Sheet1!$A$2:$A$20,Sheet1!$B$2:$D$20,"")

For spill array, you may try-

=HSTACK(XLOOKUP(A2:A8,Sheet1!A2:A20,Sheet1!B2:B20,""),
XLOOKUP(A2:A8,Sheet1!A2:A20,Sheet1!C2:C20,""),
XLOOKUP(A2:A8,Sheet1!A2:A20,Sheet1!D2:D20,""))

Lookup Dynamically Using Excel (Structured) Tables

• When you have nice contiguous table data (no empty rows, one row of headers), it is worth putting it in Excel tables and using structured references:

  =XLOOKUP([@[Part Number]],Table1[Part Name],Table1[Sequence],"")
  

  for older versions:

  =IFNA(INDEX(Table1[Sequence],MATCH([@[Part Number]],Table1[Part Name],0)),"")
  

• Select a cell or the whole data and press Crtl+T to create a structured table.

• Clear the columns before writing the formula to the first row so it is transferred to the rest of the rows.

• You only have to change Table1[Sequence] for the other two columns.

• Afterwards, format the columns (e.g. dates) as desired.

• Now, when you add a new part number, the table on Sheet2 gets updated as illustrated in the screenshot (in pink).