Write VLOOKUP to each row in a column – openpyxl

November 25, 2021

Trying to apply a vlookup formula to each row in a given column (‘B’) with the code below;

import openpyxl

path = 'test.xlsx'
wb = openpyxl.load_workbook(path)
ws2 = wb['Sheet2']

for eachrow in ws2['B']:
    print(eachrow)
    openpyxl.cell.value = '=VLOOKUP(B{0},Sheet1!A:A,1,0)'

print(ws2['B2'].value)

the print(eachrow) line gives me all the rows individually as I want them but the next line does not update the values with the provided VLOOKUP string.

print(ws2['B2'].value) returns the value None

>Solution :

This line openpyxl.cell.value = '=VLOOKUP(B{0},Sheet1!A:A,1,0)' is not doing what you think it’s doing. You already have a reference to the cell; eachrow. Instead, use:

eachrow.value = '=VLOOKUP(B{0},Sheet1!A:A,1,0)'

However, openpyxl does not evaluate excel formulas, so your call to print will only print =VLOOKUP(B{0},Sheet1!A:A,1,0)'. If you want to evaluate the formula you could look at using xlwings