import openpyxl, time
from subprocess import Popen

start = time.time()

######################ENTER VARIABLES##############################

#indicate Excel files
input = 'statistics.xlsx' #input Excel file
output = 'statistics2.xlsx' #output as new Excel file, in case of file corruption

#indicate Excel sheets
sheet1 = 'Sheet1'   #sheet for input new column
sheet2 = 'Sheet2'   #sheet to lookup

#indicate columns for lookup
#lookup 1
col1sh1 = 6     #first lookup column in sheet1
col1sh2 = 1     #first lookup column in sheet2 (lookup sheet)
#lookup 2
col2sh1 = 7
col2sh2 = 2

#input new column
col3sh1 = 11    #new column in sheet1
col3sh2 = 5     #column to transfer from sheet2 to sheet1 with earlier filters

###################################################################

file = openpyxl.load_workbook(input)

#indicate sheets in Excel
shp = file.get_sheet_by_name(sheet1)
category = file.get_sheet_by_name(sheet2)

x=0
#iterate through each row for sheet that requires new column based on lookup
for rowNum in range(2, (shp.get_highest_row()+1)):
    lookup1Sh1 = shp.cell(row=rowNum,column=col1sh1).value
    lookup2Sh1 = shp.cell(row=rowNum,column=col2sh1).value

    #iterate through each row for lookup sheet
    for rowNum2 in range(2, (category.get_highest_row()+1)):
        lookup1Sh2 = category.cell(row=rowNum2,column=col1sh2).value
        lookup2Sh2 = category.cell(row=rowNum2,column=col2sh2).value
        transfer = category.cell(row=rowNum2,column=col3sh2).value

        if lookup1Sh1 == lookup1Sh2 and lookup2Sh1 == lookup2Sh2:   #add more filters if you have more than 2 lookup values
            shp.cell(row=rowNum,column=col3sh1).value = transfer
            x+=1
            print x

file.save(output)
end = time.time()

print '\nMulti vlookup completed'
print 'Script completed in {} min'.format((end-start)/60)

Popen(output, shell=True)   #open excel output
