Image by Editor | Midjourney

When managing inventory, product catalogs, or any data across multiple Excel sheets, you often need to check if values from one sheet exist in another. While Excel provides formulas like MATCH() for this purpose, automating these checks with Python and openpyxl lets you process multiple files consistently and build more sophisticated analysis tools.

In this article, we’ll create a Python script that:
1. Sets up sample inventory data across two warehouse sheets
2. Checks which items from Warehouse A are also available in Warehouse B
3. Applies color formatting to highlight matches and mismatches

This is a common business need for inventory management, data reconciliation, or identifying gaps between datasets. Let’s see how openpyxl makes this process straightforward and efficient.

Setting Up the Inventory Data Structure

First, let’s create an Excel workbook with two sheets containing product inventory data for two different warehouses. We’ll use openpyxl to set up this foundation:

from openpyxl import Workbook
from openpyxl.styles import Font

# Create a new workbook
wb = Workbook()
ws1 = wb.active
ws1.title = "Warehouse_A"

# Create a second sheet
ws2 = wb.create_sheet(title="Warehouse_B")

# Add headers to Warehouse_A
headers1 = ["Product_ID", "Quantity"]
for col, header in enumerate(headers1, 1):
    cell = ws1.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True)

# Add data to Warehouse_A
products_A = [
    ("P1001", 150),
    ("P1002", 75),
    ("P1003", 200),
    ("P1004", 45),
    ("P1005", 120),
    ("P1006", 60)
]

for row, (product, quantity) in enumerate(products_A, 2):
    ws1.cell(row=row, column=1, value=product)
    ws1.cell(row=row, column=2, value=quantity)

# Add headers to Warehouse_B
headers2 = ["Product_ID", "Quantity"]
for col, header in enumerate(headers2, 1):
    cell = ws2.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True)

# Add data to Warehouse_B (some overlap, some different)
products_B = [
    ("P1002", 30),
    ("P1003", 150),
    ("P1005", 80),
    ("P1007", 100),
    ("P1008", 90)
]

for row, (product, quantity) in enumerate(products_B, 2):
    ws2.cell(row=row, column=1, value=product)
    ws2.cell(row=row, column=2, value=quantity)

# Save the workbook
wb.save('inventory_comparison.xlsx')

This initial code:

1. Creates a new workbook with two sheets titled “Warehouse_A” and “Warehouse_B”
2. Sets up column headers with bold formatting on both sheets
3. Populates Warehouse_A with six products (P1001-P1006)
4. Populates Warehouse_B with five products (P1002, P1003, P1005, P1007, P1008)
5. Saves the workbook to a file named “inventory_comparison.xlsx”

Notice how we’ve designed the data to have partial overlap – products P1002, P1003, and P1005 exist in both warehouses, while others are unique to each location. This will help us demonstrate the lookup functionality clearly.

When you run this code, Excel will create the following sheets:

Image By Author

Image By Author

Now that we have our basic structure in place, we’ll move on to adding the functionality that checks which products from Warehouse A also exist in Warehouse B. 

Implementing the Product Lookup Between Sheets

Now that we have our workbook with inventory data from both warehouses, let’s add functionality to check which products from Warehouse A are also available in Warehouse B. In Excel, this would typically be done using a formula like MATCH() or VLOOKUP(), but with openpyxl, we can automate this process programmatically.

from openpyxl import load_workbook
from openpyxl.styles import Font

# Load the workbook
wb = load_workbook('inventory_comparison.xlsx')
ws1 = wb["Warehouse_A"]
ws2 = wb["Warehouse_B"]

# Add a header for the existence check column in Warehouse_A
ws1.cell(row=1, column=3, value="Available in Warehouse B")
ws1.cell(row=1, column=3).font = Font(bold=True)

# Get all product IDs from Warehouse_B
products_in_B = []
for row in range(2, ws2.max_row + 1):
    product = ws2.cell(row=row, column=1).value
    if product:
        products_in_B.append(product)

# Check if each product in Warehouse_A exists in Warehouse_B
for row in range(2, ws1.max_row + 1):
    product = ws1.cell(row=row, column=1).value
    exists = product in products_in_B
    ws1.cell(row=row, column=3, value=str(exists).upper())

# Adjust column widths
for col in ws1.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    ws1.column_dimensions[column].width = adjusted_width

# Save the workbook
wb.save('inventory_comparison.xlsx')

This second code block performs several important operations:

1. Loads the existing workbook we created in the previous step
2. Adds a new column header to Warehouse_A called “Available in Warehouse B”
3. Creates a list of all products currently available in Warehouse_B
4. Loops through each product in Warehouse_A and checks if it exists in the Warehouse_B products list
5. Writes TRUE or FALSE to indicate whether each product is available in both warehouses
6. Adjusts column widths to ensure all data is visible
7. Saves the updated workbook

The key part of this code is how we check for the existence of each product:

exists = product in products_in_B

This simple Python expression replaces what would be a more complex MATCH() or VLOOKUP() formula in Excel. By building a list of all products from Warehouse B first, we can perform an efficient lookup for each product in Warehouse A.

When you run this code, the Warehouse_A sheet will be updated to include the new column showing which products exist in both warehouses:

Image By Author

This already provides useful information, but we can enhance it further with visual formatting to make the results even more intuitive at a glance.

Adding Visual Formatting for Better Readability

While the TRUE/FALSE values in our sheet are informative, adding color formatting makes it much easier to quickly scan the results. In Excel, you would typically use conditional formatting for this task. With openpyxl, we can programmatically apply similar formatting to highlight matches and mismatches.

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

# Load the workbook
wb = load_workbook('inventory_comparison.xlsx')
ws1 = wb["Warehouse_A"]

# Define fill colors
green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")  # Light green
red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")    # Light red

# Apply conditional formatting based on result
for row in range(2, ws1.max_row + 1):
    cell = ws1.cell(row=row, column=3)
    if cell.value == "TRUE":
        cell.fill = green_fill
    elif cell.value == "FALSE":
        cell.fill = red_fill

# Save the workbook
wb.save('inventory_comparison.xlsx')

This final code block adds visual enhancements to our workbook:

1. Defines two fill patterns – light green for matches and light red for mismatches
2. Loops through each result cell in the “Available in Warehouse B” column
3. Applies the appropriate fill color based on the TRUE/FALSE value
4. Saves the formatted workbook

The code uses openpyxl’s PatternFill class to define the background colors. When you run this code, your Warehouse_A sheet will now have color-coded results:

Image By Author

The color formatting provides immediate visual feedback, making it easy to identify at a glance which products are available in both warehouses. This is particularly useful when dealing with larger datasets or when presenting findings to stakeholders.

Conclusion

This tutorial demonstrates how to use openpyxl to replicate Excel’s lookup functionality across different sheets while adding visual enhancements. The approach offers several advantages over manual Excel formulas:

1. Automation – Process multiple files or large datasets efficiently
2. Consistency – Apply the same logic and formatting across all data
3. Integration – Incorporate this functionality into larger data processing workflows
4. Customization – Easily adapt the code for different business requirements

While Excel’s built-in functions like MATCH() and VLOOKUP() are powerful, using Python with openpyxl gives you more flexibility and control over how you process and present your data.

For additional Excel automation techniques using Python and openpyxl, check out our related guides on:

1. How to Effectively Work with Excel Files in Python: Pandas vs Openpyxl Guide
2. How to Create Color-Coded Progress Bars in Excel with Openpyxl
3. How to Implement Excel’s VLOOKUP with Python and Openpyxl
4. How to Format Dates in Excel Using Python and Openpyxl 
5. How to Extract Text Between Delimiters in Excel Using Python and Openpyxl