Search Excel Row Number by Value using openpyxl

How to find row numbers in Excel by using openpyxl

In this tutorial, I 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.

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):

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

The code:

```#Search Data and return cell numbers

#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

#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

#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.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!