SUMIFS & TEXTJOIN+FILTER vs XLOOKUP When Duplicate Keys Exist

Decide whether you need one row or an aggregate

The invoice INV-101 has three lines: 1200, 850 and 350. A forward exact-match XLOOKUP returns the first amount, 1200; SUMIFS returns their total, 2400. Neither operation is inherently wrong: the required output determines which to use.

XLOOKUP can be appropriate with duplicate keys when the first matching record is intentionally required. It is not a substitute for aggregating all lines. Reordering the ledger can change a first-match result while preserving its total.

This example treats IDs case-insensitively by normalizing A2:A6 into uppercase helper cells F2:F6 and uppercasing the query E2. It does not trim spaces. Keep ledger IDs as nonempty text; match the normalized ID, not a substring.

The SUMIFS and COUNTIF criteria escape literal *, ? and ~ and explicitly request equality. FILTER and exact-mode XLOOKUP use literal normalized keys. All five amounts must be numeric; negative adjustments and zero values are permitted. This whole-ledger completeness rule deliberately reports bad amounts even outside the queried invoice.

Normalize the ledger IDs in F2:F6

Enter this in F2 and fill through F6, so the relative A2 reference advances per row.

=UPPER(A2)

Sum all matching amounts

Returns zero when no rows match, but also when matching amounts sum to zero. Use the count below to distinguish those cases.

=IF(OR(NOT(ISTEXT(E2)),E2=""),"Enter a nonempty invoice ID",IF(COUNT(C2:C6)<>5,"All ledger amounts must be numeric",SUMIFS(C2:C6,F2:F6,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(E2),"~","~~"),"*","~*"),"?","~?"))))

Return only the first matching amount

The No Lines fallback distinguishes a missing key from a real zero first amount.

=IF(OR(NOT(ISTEXT(E2)),E2=""),"Enter a nonempty invoice ID",IF(COUNT(C2:C6)<>5,"All ledger amounts must be numeric",XLOOKUP(UPPER(E2),F2:F6,C2:C6,"No Lines",0)))

Join matching descriptions in ledger order

Preserves repeated descriptions; it does not deduplicate labels. Empty descriptions are skipped by TEXTJOIN. A matching set with no nonempty labels therefore produces empty text. Joined text is a readable summary, not a lossless record format, and Excel cells have a text-length limit.

=IF(OR(NOT(ISTEXT(E2)),E2=""),"Enter a nonempty invoice ID",IF(COUNT(C2:C6)<>5,"All ledger amounts must be numeric",TEXTJOIN(", ",TRUE,FILTER(B2:B6,F2:F6=UPPER(E2),"No Lines"))))

Count matching lines

A positive count plus a zero sum is a real matched net-zero result.

=IF(OR(NOT(ISTEXT(E2)),E2=""),"Enter a nonempty invoice ID",IF(COUNT(C2:C6)<>5,"All ledger amounts must be numeric",COUNTIF(F2:F6,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(E2),"~","~~"),"*","~*"),"?","~?"))))

Further reading

• Microsoft Support — TEXTJOIN function