VLOOKUP, SUMIFS and Pivot Tables in pandas

From Excel to pandas: VLOOKUP, SUMIFS and Pivot Tables

Scene: month-end on a trading floor's middle office. The blotter export lands in your inbox: ten trades, two desks, five tickers. Your manager wants the usual sheet: each trade tagged with its sector (a VLOOKUP), the Equities desk's technology exposure (a SUMIFS), and notional by sector and month (a pivot table). You can build it in Excel in ten minutes. You can also build it in pandas once, and never build it again. But the translation hides one trap that Excel would never spring on you, and it is the reason every finance team's first rule is: reconcile your totals.

What it is: Each Excel function has a pandas twin. VLOOKUP becomes merge, SUMIFS becomes a boolean mask plus .sum(), and a pivot table becomes pd.pivot_table. The difference: Excel works cell by cell and shows you every gap; pandas works on whole tables and can drop rows without a word.

The dictionary

VLOOKUP is a left merge

VLOOKUP keeps every row of your sheet and shows #N/A where the key is missing from the lookup table. In pandas that is a left merge: every left row survives, and missing matches become NaN. Two arguments make it as safe as Excel:

df = trades.merge(sectors, on="ticker", how="left", validate="many_to_one")

validate="many_to_one" raises an error if a ticker appears twice in the lookup sheet. VLOOKUP would silently take the first match; a plain merge would silently duplicate the trade. (The merge lesson earlier in this track shows that row explosion.)

The trap: NaN keys vanish from pivots

Suppose nobody has added NVDA to the sector sheet yet. Excel's pivot shows a row called (blank) holding NVDA's notional, and the grand total still ties to the blotter. pandas' groupby and pivot_table drop NaN keys by default. The NVDA trades disappear from the pivot, and the grand total is short by exactly their notional, with no warning. The fix is to label the gap, as Excel does:

df = df.fillna({"sector": "#N/A"})

Worked numbers (this lesson's blotter, in EUR thousands)

The blotter totals 800. NVDA's two trades (60 + 110 = 170) have no sector.

SUMIFS(desk = Equities, sector = Technology)
  = AAPL 120 + 70  +  MSFT 80 + 50  = 320

Pivot        2026-01   2026-02   Total
#N/A              60       110     170
Financials       240        70     310
Technology       200       120     320
Total            500       300     800   (= blotter)

With an inner merge, or a left merge without the #N/A label, the pivot's grand total is 630, not 800: a 170k break that a reviewer will find before you do.

Your Task

The starter builds all three outputs and ends with a reconciliation line. YOUR TURN marks the lookup: it uses an inner merge, so the NVDA trades are gone and the reconciliation prints BREAK.

Predict before you run: if you only change how="inner" to how="left", will the pivot reconcile? Try it, read the pivot, then finish the fix with validate="many_to_one" and the #N/A label.