CODEWITHSUNDEEP
pythonpandasopenpyxl
Max
Max

Reputation: 471

How to iterate a VLOOKUP over multiple rows?

I have the following data

Column A | Column B | M Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana | Apple | Apple | Orange | Orange | Pear | Banana |

Now I want to do a VLOOKUP on each of the data rows.. How do I do this?

This code works for one cell:

#working for one row
from openpyxl import load_workbook
wb = load_workbook(filename = 'flat_user_data.xlsx')
ws = wb.active
ws["M10"] = "=IF(ISNA(VLOOKUP(A10,'Emergency User Exclusions'!A:A,1,FALSE)),FALSE,TRUE)"
wb.save('flat_user_data.xlsx')

I've tried the following for multiple cells:

#multiple rows
from openpyxl import load_workbook
wb = load_workbook(filename = 'flat_user_data.xlsx')
ws = wb.active
for i in ws.iter_rows():
    ws["M{i}"] = "=IF(ISNA(VLOOKUP(A{i},'Emergency User Exclusions'!A:A,1,FALSE)),FALSE,TRUE)"
    wb.save('flat_user_data.xlsx')

However getting the following error ValueError: M{i} is not a valid coordinate or range

Upvotes: 0

Views: 169

Answers (1)

LeonardoVaz
LeonardoVaz

Reputation: 311

You need to put an 'f' in front of your string if you want to use variables between curly brackets.

Try the code below:

from openpyxl import load_workbook
wb = load_workbook(filename = 'flat_user_data.xlsx')
ws = wb.active
for i in ws.iter_rows():
    ws[f"M{i}"] = f"=IF(ISNA(VLOOKUP(A{i},'Emergency User Exclusions'!A:A,1,FALSE)),FALSE,TRUE)"
    wb.save('flat_user_data.xlsx')

Upvotes: 2

Related Questions