Image by Editor | Midjourney

Excel’s VLOOKUP function helps users search for values in large datasets, often used to match items between different lists or tables. While Excel provides this functionality through its built-in formulas, automating these lookups with Python and openpyxl allows you to process multiple files consistently and handle larger datasets efficiently.

Understanding VLOOKUP

VLOOKUP searches for a value in the first column of a specified range and returns a corresponding value from another column in that range. A common application is checking if items exist in a master list – exactly what we’ll implement in this article. 

For example, when VLOOKUP returns “Yes”/”No” results, it typically uses this formula structure:

=IF(ISNA(VLOOKUP(lookup_value, table_array, col_index, FALSE)), "No", "Yes")

This formula:

1. Attempts to find the lookup_value in the specified table_array
2. Returns #N/A if the value isn’t found
3. Uses IF and ISNA to convert the result to “Yes” or “No”

In this tutorial, we’ll recreate this functionality using Python and openpyxl, building a script that checks if books from a reading list are available in a library catalog. Our implementation will include color coding to make results easily scannable. 

Setting Up the Basic Structure

Let’s start by creating an Excel workbook with our sample data. We’ll organize it with two lists: our library’s available books in the first column and a reading list to check against in the second column.

from openpyxl import Workbook
from openpyxl.styles import Font

# Create a new workbook and select active sheet
wb = Workbook()
ws = wb.active
ws.title = "Book Lookup"

# Add headers with formatting
headers = ["Available Books", "Reading List", "In Library?"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True)

# Add sample books (popular titles)
library_books = [
    "The Great Gatsby",
    "1984",
    "Pride and Prejudice",
    "To Kill a Mockingbird",
    "The Catcher in the Rye",
    "Lord of the Rings",
    "Brave New World",
    "The Hobbit",
    "Fahrenheit 451",
    "Animal Farm"
]

# Add books to first column
for row, book in enumerate(library_books, 2):
    ws.cell(row=row, column=1, value=book)

# Add reading list (books to check)
reading_list = [
    "1984",
    "Dune",
    "The Hobbit",
    "The Great Gatsby"
]

# Add reading list to second column
for row, book in enumerate(reading_list, 2):
    ws.cell(row=row, column=2, value=book)

# Adjust column widths for better readability
for col in ws.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)
    ws.column_dimensions[column].width = adjusted_width

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

This initial code block:

1. Creates a new workbook titled “Book Lookup”.
2. Sets up three columns with bold headers: Available Books, Reading List, and In Library?
3. Populates the first column with ten classic books.
4. Adds four books to check in the second column.
5. Automatically adjusts column widths for optimal display.

When you open the generated Excel file, you’ll see:

Image By Author

The setup includes some books that exist in both lists (like “1984” and “The Hobbit”) and others that appear only in the reading list (like “Dune”). This mix will help demonstrate how our lookup function identifies matches and non-matches in the next section.

Adding Lookup Logic and Visual Indicators

Now that our workbook structure is set up, let’s implement the lookup functionality. We’ll add logic to check if each book in the reading list exists in our library and use color coding to make the results easy to scan.

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

# Load the workbook from step 1
wb = load_workbook('book_lookup_step1.xlsx')
ws = wb.active

# Define fill patterns for available/unavailable books
green_fill = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')  # Light green
red_fill = PatternFill(start_color='FFB6C1', end_color='FFB6C1', fill_type='solid')    # Light red

# Get all available books from first column
available_books = [ws.cell(row=r, column=1).value for r in range(2, ws.max_row + 1) 
                  if ws.cell(row=r, column=1).value is not None]

# Check each book in reading list
reading_list_row = 2
while ws.cell(row=reading_list_row, column=2).value is not None:
    book_to_check = ws.cell(row=reading_list_row, column=2).value
    
    # Check if book exists in available_books
    result_cell = ws.cell(row=reading_list_row, column=3)
    if book_to_check in available_books:
        result_cell.value = "Yes"
        result_cell.fill = green_fill
    else:
        result_cell.value = "No"
        result_cell.fill = red_fill
    
    reading_list_row += 1

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

This code performs several key operations:

1. Loads our existing workbook created in the previous step
2. Creates two fill patterns: green for available books and red for unavailable ones
3. Builds a list of all available books from the first column
4. Checks each book in the reading list against the available books list
5. Adds “Yes” or “No” in the third column with appropriate color coding

When you run this code, the Excel file updates to show:

Image By Author

The results show that “1984”, “The Hobbit”, and “The Great Gatsby” are available in our library (marked in green), while “Dune” is not available (marked in red). This visual feedback makes it easy to quickly identify which books from the reading list can be found in the library.

By combining Python’s list operations with openpyxl’s formatting capabilities, we’ve recreated Excel’s VLOOKUP functionality in a way that can be automated and applied to larger datasets or multiple files.

Conclusion

This tutorial shows how Python and openpyxl can replicate Excel’s VLOOKUP functionality while adding visual enhancements. The implementation offers several advantages over manual Excel formulas:

1. Automates lookups across multiple files
2. Adds visual indicators through color coding
3. Processes data sets of any size efficiently
4. Maintains consistent formatting across all results

The code provided here establishes a pattern you can adapt for other lookup scenarios: inventory checking, customer verification, data matching, or any task that requires comparing items between lists.

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-Style Conditional Logic with Openpyxl
4. How to Format Dates in Excel Using Python and Openpyxl
5. How to Analyze Sales Data with Python and Openpyxl