How to get common data from a large data set sheet into a small data set sheet with common entry in first column and match them accordingly

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:

Part Name	Sequence	Due Date	quantity
A1	1	11-Mar	88
A2	2	12-Mar	90
A5	1	13-Mar	41
B5	1	14-Mar	69
B3	1	15-Mar	85
Z6	1	16-Mar	97
X7	2	17-Mar	77
H6	2	18-Mar	81
AB6	2	19-Mar	79
ACF	4	20-Mar	70
FF9	4	21-Mar	83
HJ	4	22-Mar	15
907	3	23-Mar	51
U9	3	24-Mar	10
7L	1	25-Mar	61
B1	2	26-Mar	95
K7	3	27-Mar	43
JY	1	28-Mar	66
O12	4	29-Mar	28

Sheet 2:

Part Number	Issue	error	quality	supervisor	Line No.
Z6	1	0.029408618	OK	AB	12
907	2	0.061354664	OK	AB	23
B1	2	0.209159753	OK	AD	23
O12	1.5	0.862573414	UI	AD	24
ACF	2	0.403823252	OK	GY	15
A1	3	0.452562474	OK	NP	7
H69	1	0.327276608	OK	TV	8

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:

Part Number	Issue	error	quality	supervisor	Line No.	Sequence	Due Date	quantity
Z6	1	0.029408618	OK	AB	12	1	16-Mar	97
907	2	0.061354664	OK	AB	23	3	23-Mar	51
B1	2	0.209159753	OK	AD	23	2	26-Mar	95
O12	1.5	0.862573414	UI	AD	24	4	29-Mar	28
ACF	2	0.403823252	OK	GY	15	4	20-Mar	70
A1	3	0.452562474	OK	NP	7	1	11-Mar	88
H69	1	0.327276608	OK	TV	8

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

Solution

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,""))