2 Examples to Search Excel Row Number by Value using openpyxl

In this tutorial, we will show you how to search an Excel sheet by value or based on certain criteria and return the cell number(s) if records are found.

How to find row numbers in Excel by using openpyxl

In this tutorial, we will show you how to search an Excel sheet by value or based on certain criteria and return the cell number(s) if records are found.

For example, returning Sheet “Cell” numbers (and/or values) like D5, D6, F6 etc. for names that start with “A”.

Similarly, in a Product Detail sheet, you may want to get all products whose prices are more than $5.

Another example can be, finding the “Cells”/values of products whose quantity is less than 10 and so on.

So let us start with a basic example.

Our Sample Test Sheet

We have a sample test sheet that we will use for all our examples below. The Sheet name is: “Product Information” and this is in the “test_Excel.xlsx” Workbook. (See the image below with sample data):

Excel-Find-Row-Sample

Loading the Workbook and sheet

First, let us load the Workbook and sheet for our examples below:

The code:

#Search Data and return cell numbers
from openpyxl import load_workbook

#Loading sample Workbook

wb_access = load_workbook('test_Excel.xlsx')

#Accessing Product Information Sheet

sheet = wb_access.active

Find Cell Number for the given Product Name

In our first example, we will display the Cell number of the Product Name: “Maze”.

We will use:

irer_rows function which syntax is:

iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)

First, have a look at the code and result and we will explain how it worked:

The code:

# Searching Data Examples

from openpyxl import load_workbook,styles

#Loading required workbook

wb_access = load_workbook('test_Excel.xlsx')

#Accessing Active Worksheet

sheet = wb_access.active

#Accessing Cell Logic Here

for row in sheet.iter_rows(min_row=1, min_col=2, max_row=11, max_col=5, values_only=False):

    for cell in row:

        if cell.value =="Maze":

            print("Record Exists at ", sheet.cell(row=cell.row, column=cell.column))

Result:

Record Exists at  <Cell 'My_sheet1'.B5>

You can see in the above sample sheet that “Maze” exists in the B5 cell.

Following are the parameters of the iter_rows function:

  • min_col (int) – Index of the minimum column where the search should start. The index starts at 1.
  • min_row (int) – It’s the minimum row to start searching from. We specified 2, as the first row is headers.
  • max_col (int) – maximum column number to look at.
  • max_row (int) – Maximum row number. We gave 11 as we have a total of 11 rows in our sample sheet.
  • values_only (bool) – whether only cell values should be returned

Return type:     

Generator

Search row numbers for the quantity column

In this example, we will search the cell numbers of products whose quantity is greater than or equal to 15.

The code:

#Search Data and return cell numbers

from openpyxl import load_workbook
#Loading sample Workbook

wb_access = load_workbook('test_Excel.xlsx')

#Accessing Product Information Sheet

sheet = wb_access.active

#Searching Cell Logic Here

for row in sheet.iter_rows(min_row=2, min_col=4, max_row=11, max_col=4, values_only=False):

    for cell in row:

        if cell.value >=15:

            print("Record Exists at row #", sheet.cell(row=cell.row, column=cell.column))

Output:

Record Exists at row # <Cell 'My_sheet1'.D2>

Record Exists at row # <Cell 'My_sheet1'.D6>

Record Exists at row # <Cell 'My_sheet1'.D7>

Record Exists at row # <Cell 'My_sheet1'.D8>

Record Exists at row # <Cell 'My_sheet1'.D10>

Record Exists at row # <Cell 'My_sheet1'.D11>

You can compare these cells with the above-shared sample sheet and see which cell quantity is more than or equal to 15.

Notice our criteria in this line:

sheet.iter_rows(min_row=2, min_col=4, max_row=11, max_col=4, values_only=False)

 

Minimum row = 2 means start searching from the second row as our first row is the header.

Minimum column is 4 as we know our quantity column is number 4th.

Similarly, the maximum column is 4 as we only want to search in the quantity column.