excel lookups

[Deephect]

I have two excel sheets in the same book, each row contains a lookup value from sheet2.
Sheet2 contains lookups and all lookup values, Sheet1 contains the lookup and selection from the lookup
For each row in sheet1 i want to copy row and then replace with all values in the corresponding lookup

Sheet one

Output:
TestColOne	TestColTwo	TestColThree
BMW             	UK	            High

Sheet two

Output:
TestColOne	TestColTwo	TestColThree
Volkswagen	   China	             High
BMW	           Canada	         Low
Audi	                UK	           Medium

sheet 1 after script runs

Output:
TestColOne	TestColTwo	TestColThree
BMW	                 UK	           High
Volkswagen	     UK	           High
Audi	                 UK	           High
BMW	                 UK	           High
BMW	              Canada	       High
BMW	               China	           High
BMW	                UK	           Low
BMW	                UK	          Medium

This is what i have but i am not getting the desider result, please help

import pandas as pd

# load workbook
workbook = pd.ExcelFile("Book1.xlsx")

# load sheet 1 and sheet 2 as dataframes
sheet1 = workbook.parse("Sheet1")
sheet2 = workbook.parse("Sheet2")

# initialize empty dataframe to store new rows
new_rows = pd.DataFrame()

# iterate over columns in sheet 2
for col in sheet1.columns:
    # iterate over rows in sheet 1
    for i, row in sheet1.iterrows():
        # create a new row with the values from the current row in sheet 1
        new_row = row.copy()
        # replace the value in the current column with the value from the corresponding cell in sheet 2
        new_row[col] = sheet2.loc[i, col]
        print(new_row[col])
        # append the new row to the dataframe of new rows
        new_rows = new_rows.append(new_row, ignore_index=True)

# append the new rows to sheet 1
sheet1 = sheet1.append(new_rows, ignore_index=True)

# save the updated sheet 1 to the same workbook
with pd.ExcelWriter("workbook.xlsx") as writer:
    sheet1.to_excel(writer, sheet_name='Sheet1', index=False)
    sheet2.to_excel(writer, sheet_name='Sheet2', index=False)

buran write Jan-13-2023, 02:19 PM:
Please, use proper tags when post code, traceback, output, etc. This time I have added tags for you.
See BBcode help for more info.