Look Up All Matches for a Key Instead of the First

A customer list needs each customer's order numbers next to their name. VLOOKUP and XLOOKUP both return the first match, so a customer with five orders shows one order number and nothing hints that four are missing. Excel 365 can return all matches with FILTER, usually wrapped in TEXTJOIN to fit them into one cell; older workbooks use helper columns or array formulas. In pandas, "all matches" is a natural operation, but it has several useful shapes: every match joined into one text cell, a count and total next to it, just the second or latest match, each match in its own column, or one output row per match. This page builds each shape, starting from the first-match result a spreadsheet gives, and explains when to use which. It belongs to Merging and Joining Excel DataFrames.

Prerequisites

pip install pandas openpyxl

crm.xlsx has a Customers sheet with four customers and an Orders sheet with eight orders. Oakridge Farms has four orders, Café Señor and Zenith two each, and Haddad & Sons none — the cases a lookup has to handle. LibreOffice (soffice) recalculates the spreadsheet formulas in Step 1.

Step 1 — What the spreadsheet lookups return

Write a first-match VLOOKUP and an all-matches TEXTJOIN(FILTER(...)) for each customer and recalculate:

import subprocess
from openpyxl import load_workbook

wb = load_workbook("crm.xlsx")
ws = wb["Customers"]
ws["C1"], ws["D1"] = "VLOOKUP (first)", "TEXTJOIN of FILTER (all)"
for r in range(2, ws.max_row + 1):
    ws[f"C{r}"] = f'=IFERROR(INDEX(Orders!$A$2:$A$9,MATCH(A{r},Orders!$B$2:$B$9,0)),"")'
    ws[f"D{r}"] = f'=IFERROR(_xlfn.TEXTJOIN(", ",TRUE,_xlfn._xlws.FILTER(Orders!$A$2:$A$9,Orders!$B$2:$B$9=A{r})),"")'
wb.save("crm_formulas.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "crm_formulas.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/crm_formulas.xlsx", data_only=True)["Customers"]
excel = {calc[f"A{r}"].value: (calc[f"C{r}"].value, calc[f"D{r}"].value) for r in range(2, calc.max_row + 1)}
for k, v in excel.items():
    print(k, v)

The first-match lookup — written here as INDEX/MATCH, which behaves like VLOOKUP but can return a column to the left — gives SO-101 for Oakridge Farms and nothing more. The TEXTJOIN(FILTER(...)) version lists all four orders. Two file-format details matter when writing these formulas from Python: TEXTJOIN needs the _xlfn. prefix and FILTER the longer _xlfn._xlws. prefix, otherwise the spreadsheet shows #NAME?. Haddad & Sons, with no orders, gets an empty string from the IFERROR wrappers.

Step 2 — All matches in one cell

The pandas version of TEXTJOIN(FILTER(...)) groups the matching table by key and joins the values, then maps the result onto the lookup table:

import pandas as pd

customers = pd.read_excel("crm.xlsx", sheet_name="Customers")
orders = pd.read_excel("crm.xlsx", sheet_name="Orders").sort_values(["Customer", "Date"])

joined = orders.groupby("Customer")["Order"].agg(", ".join)
customers["Orders"] = customers["Customer"].map(joined).fillna("")
print(customers.to_string(index=False))
print("matches spreadsheet:", all(customers.set_index("Customer")["Orders"][k] == (v[1] or "") for k, v in excel.items()))

groupby(...).agg(", ".join) concatenates every order number per customer into one string; map looks each customer up in that result, and fillna("") gives customers without orders an empty cell, like the IFERROR in the formula. (When the recalculated workbook is read back, that empty string arrives as None — openpyxl does not store empty strings — so the comparison treats the two as equal.) Sorting by date first means the joined list is in date order, which a reader expects — the spreadsheet's FILTER returns matches in sheet order, which happens to be the same here. This shape is good for a readable report column; it is a poor shape for further analysis, because the values are now text.

Step 3 — Counts, totals and the latest match

Often what is really wanted is not the list itself but facts about it: how many matches, their total, the most recent one. Named aggregation produces them all in one pass:

summary = orders.groupby("Customer").agg(
    order_count=("Order", "size"),
    total=("Amount", "sum"),
    first_order=("Date", "min"),
    latest_order=("Order", "last"),
)
customers = customers.merge(summary, on="Customer", how="left")
customers["order_count"] = customers["order_count"].fillna(0).astype(int)
print(customers.drop(columns="Name").to_string(index=False))

Each customer row now carries its number of orders — four for Oakridge Farms, totalling 3,230 — their total value, the date of the first one and the latest order number (the last after sorting by date). A left merge keeps every customer, and customers without orders get zero orders and missing values elsewhere — the honest result, rather than a zero total that could be confused with orders worth nothing. In a spreadsheet this would be COUNTIFS, SUMIFS, MINIFS and an XLOOKUP searching from the end; see AVERAGEIFS, MAXIFS and MINIFS Equivalents in pandas.

Step 4 — The nth match, or each match in its own column

To pick the second match, or to spread matches across columns, number the matches within each key with cumcount:

orders["n"] = orders.groupby("Customer").cumcount() + 1
second = orders.loc[orders["n"] == 2].set_index("Customer")["Order"]
print("second order:", dict(zip(customers["Customer"], customers["Customer"].map(second))))

wide = orders.pivot(index="Customer", columns="n", values="Order").add_prefix("Order ")
print(customers[["Customer"]].merge(wide, left_on="Customer", right_index=True, how="left").to_string(index=False))

cumcount() + 1 numbers each customer's orders 1, 2, 3 … in date order, so "the second match" is a filter on n == 2 — the job people use INDEX with SMALL(IF(...)) array formulas for in older Excel. Pivoting on that number puts each match in its own column — Order 1, Order 2, … — which suits a fixed-width report but becomes unwieldy when one key has dozens of matches. Numbering rows within groups is covered in Number Rows Within Each Group in pandas.

Step 5 — One row per match: the one-to-many merge

When the goal is analysis rather than a display column, keep one row per match. A plain merge does exactly that:

detail = customers[["Customer", "Name"]].merge(orders[["Customer", "Order", "Date", "Amount"]], on="Customer", how="left")
print(detail.to_string(index=False))
print("rows:", len(detail), "| customers:", detail["Customer"].nunique())

The merge repeats each customer once per order, keeping every match as its own row with all its columns — nine rows here: eight orders plus Haddad & Sons with empty order fields, thanks to how="left". This is the shape for pivots, filters and charts. It is also the one that surprises people who expected a lookup: the row count grows. That growth is correct for a one-to-many relationship, but when a lookup table is supposed to have one row per key, an unexpected increase means duplicates — which is what validate="many_to_one" catches, as described in Validate a Merge to Avoid Duplicate Rows.

Which shape to use

The shape follows from the question. If a person will read the result next to each key, join the matches into text — but keep the list short, or show a count with the first few. If the question is "how many" or "how much", aggregate and never build the text at all. If one specific match matters — the first order, the latest invoice, the second contact — number the matches and filter. And if anything will be calculated, filtered or pivoted afterwards, keep one row per match and summarise at the end. Building the joined text early and then trying to analyse it is the most common way these workflows go wrong: splitting "SO-101, SO-103" back apart to count it undoes work that groupby would have done directly.

Common pitfalls

• Trusting a first-match lookup. It hides every match after the first; check how many matches exist.
• Joined text used for analysis. Keep a row-per-match table for anything you calculate.
• Unsorted matches. Sort before joining or numbering so "first" and "latest" mean something.
• Customers with no matches dropped. Use map or a left merge so they stay, with explicit blanks.
• Missing _xlfn prefixes. TEXTJOIN and FILTER written from Python need them.

Conclusion

A first-match lookup is wrong whenever a key can match more than once. In pandas, choose the shape that fits the job: join all matches into one cell with groupby().agg(", ".join) and map for a readable report column, aggregate counts and totals per key for a summary, number matches with cumcount to pick the nth or spread them into columns, and keep one row per match with a left merge for analysis. Sort first so the order of matches is meaningful, and keep keys without matches visible as blanks.

Frequently asked questions

How do I return all matches instead of the first like VLOOKUP does? Group the matching table by the key, aggregate the values, for example with ", ".join, and map the result onto the lookup table. In Excel 365, TEXTJOIN with FILTER does the same.

How do I get the second match for a key in pandas? Sort the data, number rows within each key with groupby().cumcount() + 1, and filter on number 2.

Why does my merge return more rows than my lookup table? The key matches several rows in the other table, so the merge returns one row per match. That is correct for one-to-many data; use validate to catch it when keys should be unique.

How do I count matches per key? Use groupby on the key with size or count, then map or merge the result onto the lookup table, filling keys with no matches with 0.

Related

• Up one level: Merging and Joining Excel DataFrames — combining tables.
• XLOOKUP Equivalent in pandas — the single-match lookup.
• TEXTJOIN Equivalent in pandas — joining text more generally.
• Merge Two Excel Files on a Common Column in Python — merges from the beginning.