2 Examples to use Excel VLOOKUP formula in openpyxl

Using Excel formulas in openyxl library

In this tutorial, we will show you using 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 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, 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