Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Write Lookup Formulas (XLOOKUP, VLOOKUP, INDEX/MATCH) with openpyxl

Generate live lookup formulas that pull prices, names or rates from another sheet: VLOOKUP with an exact match, INDEX/MATCH, and XLOOKUP — which must be written with the _xlfn prefix or it shows #NAME? — plus absolute ranges that survive copying, not-found handling, tiered approximate matches, a named lookup range, and a LibreOffice check that every formula returns what pandas expects.
Formulas in workbooksJoins, lookups & comparisonsopenpyxl

When a report is computed in pandas, a lookup is a merge and the workbook only needs the result. But some workbooks are meant to stay alive: an order form where the price appears when someone types a product code, a forecast where changing a rate on the assumptions sheet updates every line. Those need real lookup formulas in the cells. Writing them from Python is mostly string building, with three things to get right: anchoring the lookup range so the formula can be repeated down a column, deciding what a missing key should show, and — for XLOOKUP — a file-format detail that makes the difference between a working formula and #NAME?. This page belongs to Working with Excel Formulas in Python.

Prerequisites

Bash
pip install openpyxl pandas

order_form.xlsx has an Orders sheet with SKUs and quantities (one SKU, Z-99, is not in the price list), a Prices sheet, and a Discounts sheet of quantity tiers. LibreOffice (soffice) recalculates the result in Step 5.

Step 1 — VLOOKUP with an exact match

Python
from openpyxl import load_workbook

wb = load_workbook("order_form.xlsx")
ws = wb["Orders"]
ws["C1"] = "Price (VLOOKUP)"
last = ws.max_row
for r in range(2, last + 1):
    ws[f"C{r}"] = f'=IFERROR(VLOOKUP(A{r},Prices!$A$2:$C$5,3,FALSE),"not found")'
print(ws["C2"].value)

Two details make this formula safe to repeat down the column. The $ signs anchor the lookup range — Prices!$A$2:$C$5 stays the same on every row while A{r} moves — exactly what Excel's F4 key does. And the fourth argument, FALSE, asks for an exact match; leaving it out makes VLOOKUP assume a sorted table and return the nearest smaller key, which for product codes is quietly wrong. IFERROR replaces the #N/A of a missing SKU with readable text.

Step 2 — INDEX/MATCH

VLOOKUP can only return columns to the right of the key and breaks when someone inserts a column inside the table. INDEX/MATCH names the result column directly:

Python
ws["D1"] = "Product (INDEX/MATCH)"
for r in range(2, last + 1):
    ws[f"D{r}"] = f'=IFERROR(INDEX(Prices!$B$2:$B$5,MATCH(A{r},Prices!$A$2:$A$5,0)),"not found")'
print(ws["D2"].value)

MATCH(…, 0) finds the row of an exact match, and INDEX takes that row from the product column. It works in every Excel version and in LibreOffice, which makes it the safest choice for files with an unknown audience.

How INDEX/MATCH finds the product for one order row The SKU S-02 in the order row is matched against the SKU column of the Prices sheet with MATCH, which returns position 2. INDEX then takes the second value of the Product column, Saw. Orders!A2 = S-02 the key MATCH(…, 0) position 2 in Prices!A2:A5 INDEX(Prices!B2:B5, 2) second product Saw shown in Orders!D2

Step 3 — XLOOKUP needs the _xlfn prefix

XLOOKUP combines both and has a built-in not-found argument. But it was added to Excel in 2019, and functions added since Excel 2010 are stored in the file with a prefix, _xlfn.. Excel shows XLOOKUP to the user; the file contains _xlfn.XLOOKUP. openpyxl writes exactly the text you give it, so write the stored form:

Python
ws["E1"] = "Price (XLOOKUP)"
ws["F1"] = "Price (XLOOKUP, no prefix)"
for r in range(2, last + 1):
    ws[f"E{r}"] = f'=_xlfn.XLOOKUP(A{r},Prices!$A$2:$A$5,Prices!$C$2:$C$5,"not found")'
    ws[f"F{r}"] = f'=XLOOKUP(A{r},Prices!$A$2:$A$5,Prices!$C$2:$C$5,"not found")'
wb.save("order_form_lookups.xlsx")
print(ws["E2"].value)

Column F is the mistake on purpose: without the prefix, the spreadsheet treats XLOOKUP as an unknown name. The same rule applies to FILTER, UNIQUE, LET, IFS, TEXTJOIN and the other newer functions; the full list and fix are in Fix #NAME? Errors for New Excel Functions Written by Python.

What is stored versus what Excel shows Written as _xlfn.XLOOKUP, the formula is recognised and Excel displays XLOOKUP with the right price. Written as plain XLOOKUP, the stored name is unknown and the cell shows =_xlfn.XLOOKUP(…) stored form of a post-2010 function Excel displays =XLOOKUP(…) result 45.5 =XLOOKUP(…) not a known stored name cell shows #NAME? easy to miss in testing

Step 4 — Tiered lookups with an approximate match

Discount tiers are the case where approximate matching is exactly right: a quantity of 12 should get the 10-and-over discount. The tier table must be sorted ascending; then VLOOKUP with TRUE (or XLOOKUP with match mode -1, "exact or next smaller") returns the tier the quantity falls into:

Python
ws["G1"], ws["H1"] = "Discount", "Discount (XLOOKUP)"
for r in range(2, last + 1):
    ws[f"G{r}"] = f"=VLOOKUP(B{r},Discounts!$A$2:$B$5,2,TRUE)"
    ws[f"H{r}"] = f"=_xlfn.XLOOKUP(B{r},Discounts!$A$2:$A$5,Discounts!$B$2:$B$5,0,-1)"
    ws[f"G{r}"].number_format = ws[f"H{r}"].number_format = "0%"
wb.save("order_form_lookups.xlsx")

The same idea in pandas is merge_asof, covered in Approximate Match VLOOKUP in pandas.

Step 5 — Recalculate and check against pandas

A wrong column index or a missing $ produces numbers, not errors, so compare the formulas' results with an independent pandas calculation:

Python
import subprocess
import pandas as pd

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc",
                "order_form_lookups.xlsx"], check=True, capture_output=True)
got = pd.read_excel("recalc/order_form_lookups.xlsx", sheet_name="Orders")
print(got.to_string(index=False))

price_list = pd.read_excel("order_form.xlsx", sheet_name="Prices")
expected = pd.read_excel("order_form.xlsx", sheet_name="Orders").merge(price_list, on="SKU", how="left")
found = expected["Price"].notna()
assert (got.loc[found, "Price (VLOOKUP)"].astype(float) == expected.loc[found, "Price"]).all()
assert (got.loc[found, "Price (XLOOKUP)"].astype(float) == expected.loc[found, "Price"]).all()
print("formulas agree with pandas for", int(found.sum()), "orders; not found:", got.loc[~found, "SKU"].tolist())

The VLOOKUP, INDEX/MATCH and prefixed XLOOKUP columns agree with the pandas merge, the unknown Z-99 shows "not found" in all three, the unprefixed column holds #NAME? in every cell (pandas reads error values as NaN, so it prints blank), and the discount columns give 5% for 5 units and 10% for 12. Running this comparison in the report's tests turns a whole class of silent formula errors into failing tests.

The order sheet after recalculation The Orders sheet with SKU, Qty, the VLOOKUP price, the product from INDEX/MATCH, the prefixed XLOOKUP price, the unprefixed XLOOKUP showing A B C D E F G 1 SKU Qty VLOOKUP INDEX/MATCH XLOOKUP no prefix Discount 2 S-02 5 45.5 Saw 45.5 #NAME? 5% 3 H-03 12 14 Hose 14 #NAME? 10% 4 D-01 3 89 Drill 89 #NAME? 0% 5 Z-99 1 not found not found not found #NAME? 0%

Step 6 — Name the lookup range

Hard-coded ranges like Prices!$A$2:$C$5 stop at row 5; add a product and the formulas miss it. A defined name gives the range one place to change, and makes formulas readable:

Python
from openpyxl.workbook.defined_name import DefinedName

wb = load_workbook("order_form.xlsx")
wb.defined_names["PriceTable"] = DefinedName("PriceTable", attr_text="Prices!$A$2:$C$1000")
ws = wb["Orders"]
ws["C1"] = "Price"
for r in range(2, ws.max_row + 1):
    ws[f"C{r}"] = f'=IFERROR(VLOOKUP(A{r},PriceTable,3,FALSE),"not found")'
wb.save("order_form_named.xlsx")
print(ws["C2"].value, "|", load_workbook("order_form_named.xlsx").defined_names["PriceTable"].attr_text)

The name covers rows up to 1,000, so new products are picked up without touching any formula. An Excel table with structured references is the other common solution, described in Use Structured Table References in Formulas from Python, and defined names in general in Create a Named Range in Excel with openpyxl.

Step 7 — Protect the lookup table

A live lookup is only as good as its table, and the easiest way to break an order form is to edit or sort the price list by accident. Hide the lookup sheets from casual view and protect them, while leaving the input cells on the order sheet editable:

Python
from openpyxl.styles import Protection

wb = load_workbook("order_form_named.xlsx")
for name in ("Prices", "Discounts"):
    wb[name].protection.sheet = True
    wb[name].sheet_state = "hidden"
orders = wb["Orders"]
for r in range(2, 101):
    orders[f"A{r}"].protection = Protection(locked=False)
    orders[f"B{r}"].protection = Protection(locked=False)
orders.protection.sheet = True
wb.save("order_form_protected.xlsx")
print([(ws.title, ws.sheet_state, ws.protection.sheet) for ws in load_workbook("order_form_protected.xlsx")])

The SKU and quantity columns stay editable for a hundred rows; everything else — including the formula columns — is locked, so nobody can overwrite a formula with a typed price. Sheet protection without a password is a guard against accidents, not security; the details are in Lock Cells and Protect a Sheet with openpyxl.

Performance notes

Lookups are cheap until they are numerous and wide. A few thousand VLOOKUPs against a few hundred rows recalculate instantly; tens of thousands against a 100,000-row table, each scanning for an exact match, make every edit pause. If the workbook is large, keep lookup ranges tight (a defined name sized to the data rather than whole columns), prefer INDEX/MATCH or XLOOKUP over repeated VLOOKUPs that return several columns from the same row, and ask whether the lookup needs to be live at all.

Common pitfalls

  • Missing $ on the lookup range. Without anchors, a formula copied down shifts its table too.
  • VLOOKUP without FALSE. It defaults to approximate matching and returns wrong keys from unsorted tables.
  • XLOOKUP without _xlfn.. The cell shows #NAME?.
  • Approximate matches on unsorted tiers. Sort the tier table ascending, or the result is undefined.
  • Thousands of lookups over big ranges. Workbooks slow down noticeably; if nobody edits the keys, do the lookup in pandas and write values.

Conclusion

Lookup formulas from Python are strings with a few rules: anchor the lookup range with $, ask for exact matches (FALSE in VLOOKUP, 0 in MATCH), handle missing keys with IFERROR or XLOOKUP's not-found argument, and write XLOOKUP as _xlfn.XLOOKUP. Use approximate matching only for sorted tier tables, give the lookup range a name so it can grow, and recalculate in LibreOffice to check the formulas against the same lookup in pandas.

Frequently asked questions

How do I write a VLOOKUP formula with openpyxl? Assign the formula text to the cell as a string that starts with an equals sign, with the lookup range anchored by $ signs and FALSE as the last argument for an exact match, exactly as you would type it in Excel.

Why does XLOOKUP written by openpyxl show #NAME? Functions added after Excel 2010 are stored in the file with an _xlfn. prefix. Write =_xlfn.XLOOKUP(...) and Excel will display and evaluate it as XLOOKUP.

Should I use VLOOKUP, INDEX/MATCH or XLOOKUP in generated workbooks? INDEX/MATCH works in every Excel version and LibreOffice and survives inserted columns. XLOOKUP is clearer but needs Excel 2021 or 365 and the _xlfn. prefix. VLOOKUP is simplest but can only return columns to the right of the key.

How do I check that generated lookup formulas are correct? Recalculate the workbook with LibreOffice (soffice --headless --convert-to xlsx), read the results with pandas, and compare them with a pandas merge of the same tables.

More on formulas in workbooks

More on formulas in workbooks →