CODEWITHSUNDEEP
pythonexcelopenpyxl
Emac
Emac

Reputation: 1167

Openpyxl Python - Vlookup Iterate through rows

I'm trying to automate a daily report we have, and I'm using a query to pull in data and writing it in Excel using openpyxl, and then doing a vlookup in openpyxl to match a cell value. Unfortunately I'm hung up on how to iterate through the rows to find the cell value to look up.

for row in ws['E5:E91']:
    for cell in row:
        cell.value = "=VLOOKUP(D5, 'POD data'!C1:D87, 2, FALSE)"

It works except I don't know how to change the D5 value to look up D6, D7, D8, etc. depending on the row I'm on. I'm honestly at a loss for how to best approach this. Obviously I don't feel like writing the formula out for every single row, and there's other columns I'd like to do this for once I get it.

Upvotes: 1

Views: 11234

Answers (1)

Stephen Rauch
Stephen Rauch

Reputation: 49784

Using your example, you can do:

for row in ws['E5:E91']:
    for cell in row:
       cell.value = "=VLOOKUP(D{0}, 'POD data'!C1:D87, 2, FALSE)".format(cell.row)

Upvotes: 5

Related Questions