Using Excel formulas in openyxl library
In this tutorial, we will show you how to use VLOOKUP Excel function in openpyxl – A Python library.
For the examples, we will use the following sample sheet:
An example of Excel VLOOKUP using openpyxl
- In the program below, we will load the “openpyxl_ex.xlsx” Workbook.
- Then we specified the “Products” sheet.
- After that, we assigned the VLOOKUP formula to the E16 cell.
Code:
from openpyxl import load_workbook
wb_vlookup = load_workbook('openpyxl_ex.xlsx')
sheet = wb_vlookup.active
#Specify cell where VLOOKUP formula is saved - it is E16
cell = sheet.cell(row=16, column=5)
#Providing Search term, Range, and column to search in
cell.value = "=VLOOKUP(B2,B2:D11,3,FALSE)"
#Save the sheet
wb_vlookup.save('openpyxl_ex.xlsx')
Output:
How did it work?
- The purpose of the VLOOKUP function above is to return the Status of the given product.
- We searched for B2 cell that contains “Wheat”
- Our range to search is B2:D11
- The 3 in VLOOPUP says to search in the Status column
- E16 displays the result
You can see, the E16 cell contains the status.
Taking user input for the look_up value
Let us make it more dynamic by taking the user input for the VLOOKUP’s look_up value argument.
As you execute the program below, it asks you to enter the product name for Status.
After entering a product, it will execute the VLOOKUP function and write the status in the E16 cell:
from openpyxl import load_workbook
wb_vlookup = load_workbook('openpyxl_ex.xlsx')
sheet = wb_vlookup.active
#Takign user inpot for product name
product_name = input("Enter a Product to Check Status: ")
#Specify cell where VLOOKUP formula is saved - it is E16
cell = sheet.cell(row=16, column=5)
#Using product name in the VLOOKUP with concatenation
cell.value = '=VLOOKUP("'+ product_name +'",B2:D11,3,FALSE)'
#Save the sheet
wb_vlookup.save('openpyxl_ex.xlsx')
Output:
Enter a Product to Check Status: Dinner Set
Be careful when using concatenation, as entered text is passed in double quotes to the VLOOKUP.
