Add categories with XLOOKUP without accepting ambiguous mappings

Attach a category only when a reference key is unique and its label is present. Test duplicate mappings, missing labels and literal wildcard characters.

An order export has product codes but no category names. A reference table can supply those names, but a lookup's first answer is not proof that the mapping is valid. If A04 appears twice with different categories, accepting the first result can silently misclassify the order.

This exercise keeps all input rows and fills a category only after checking that the code has one reference match and a nonempty label. It extends list comparison into a controlled enrichment task. It does not append files, delete duplicate rows or aggregate several reference matches.

Treat the result as a review queue

Release only rows whose lookup status is ready. A missing key needs a verified reference entry; a duplicate key needs a decision about the authoritative entry; a blank category needs a real label, not a placeholder chosen to make the check pass. Keep the status with the result when handing the sheet to another person. If the reference list changes, recalculate and review the exceptions again before exporting.

Prepare the input and reference table

Download the eight input records, seven reference records, expected output and dated test record. The practice pack includes these examples and the totals exercise.

Use an Excel edition that supports XLOOKUP. Our test uses Microsoft 365 for Mac. Microsoft lists availability on the linked documentation page; Excel 2016 and Excel 2019 do not support XLOOKUP. A #NAME? result is not an unmatched code—check function availability and spelling.

On a blank worksheet place the input in A1:B9 and the reference in G1:H8. Both files include their header row. Keep F as a spacer. The input columns are row_id and code; the reference columns are code and category. Leave the missing input code in B7 and missing category in H4 genuinely empty. Do not type the word blank into them.

Preserve codes as Text using the identifier import guide. For a formula-only practice run, format B2:B9 and G2:G8 as Text before entering the values. Keep C:E as General. A numeric code that has already lost zeros must be reloaded from the original; a lookup cannot reconstruct it.

State the matching contract

This category dictionary treats letter case as equivalent. A01 and a01 mean the same code. Characters such as the asterisk in A*1 are literal, not wildcards. Leading and trailing spaces still matter, and blank input codes are never valid matches.

This differs from the case-sensitive contract in the list-comparison tutorial. Use the rule your source system actually defines. If case distinguishes real products, do not use this recipe unchanged. Retain the source key and resolve any space normalization before joining tables. Both input and reference keys must be text; this exercise does not mix numeric and text versions of a key.

Count matches before returning a category

Enter match_count, status and category in C1:E1. In C2 enter:

=IF(B2="","",SUMPRODUCT(--($G$2:$G$8=B2)))

The equality comparison checks the bounded reference range literally and without case sensitivity. SUMPRODUCT counts the TRUE results after the double minus converts them to numbers. The blank-key guard prevents a missing code from matching an empty reference cell.

In D2 enter:

=IF(B2="","missing code",IF(C2=0,"unmapped",IF(C2>1,"duplicate mapping",IF(SUMPRODUCT(--($G$2:$G$8=B2),--($H$2:$H$8=""))>0,"missing category","ready"))))

Read the checks in order: missing input, no match, multiple matches, then an empty label on the unique match. Only ready means it is reasonable to return the reference category. Even two duplicate rows with the same label are held for review; this workflow requires a unique dictionary key.

In E2 enter:

=IF(D2="ready",XLOOKUP(B2,$G$2:$G$8,$H$2:$H$8,"",0),"")

The final 0 requests exact matching rather than wildcard or approximate matching. The status column supplies the reason whenever the category is withheld. Do not copy only the category column into a handoff and lose those explanations.

Fill C2:E2 through row 9. Verify the last formulas refer to B9, C9 and D9, while both reference ranges still end at row 8. Use your installation's argument separator if it expects semicolons. These formulas use ordinary Enter; no special array-entry keystroke is needed in the tested version.

Inspect every outcome

Expect four ready rows and four exceptions, with all eight original rows retained. The word empty in this table describes a genuinely blank output, not a string to type into the worksheet. The expected CSV uses empty fields.

A03 demonstrates why finding a key is not enough: its category is missing. A04 demonstrates why a first-match result would be misleading: Hardware and Software are competing answers. A99 must not inherit the nearby A00 category Unassigned. A*1 must resolve to Special without matching A01 merely because both start with A and end with 1.

Correct the dictionary with the data owner

Keep the raw reference file unchanged and make authorized corrections in a working copy. For A04, establish whether the key is wrong, the duplicate is accidental or the business relationship is genuinely one-to-many. If multiple categories are valid, define a different output structure; do not silently choose the first or remove a legitimate row.

On a practice copy, changing the second A04 at G6 to A05 makes A04 unique. L04 then becomes ready with Hardware. Filling H4 with Services makes L03 ready with Services. Restore G6 to A04 and clear H4 to reproduce the original expected output. These are test edits, not rules for correcting business data.

A category containing only spaces is not an empty cell and will pass the blank check. Inspect reference labels for whitespace before use. Numeric zero would also count as a nonempty label; this recipe assumes category labels have already been checked as meaningful text. Never use an unexplained default category to hide an unmapped code.

Extend the workflow and hand it off

For longer inputs, fill C:E through every source row. If the dictionary grows, extend all three formulas' absolute reference ranges together. Keep key and category ranges aligned and bounded. The counts compare each input against the reference list, so very large datasets need a more scalable, independently tested join workflow.

Check that the number of output records still equals the input count. The number of ready rows plus exception rows must account for every input. Resolve or explicitly report exceptions before using the categories for group totals; otherwise a category summary may hide unclassified records.

Save your working spreadsheet with its formulas. When exporting a delivery CSV, retain row_id, the original code, status and category as required by the recipient, then reopen and check the export. Recheck the lookup whenever the dictionary changes.

Actual test record

On October 12, 2026, the eight input rows and seven reference rows were entered in a separate Excel for Mac 16.113.2 workbook. Native reads confirmed all match counts, statuses and categories against the downloadable expected file. The tests included a case variant, a literal asterisk, an empty input, an absent key, conflicting duplicate keys and an empty reference label.

Changing G6 to A05 released L04 as Hardware; filling H4 with Services released L03. Both inputs were restored and the original eight results were verified again. The image is an actual Excel range capture with English worksheet content. The surrounding interface was Chinese. This is a worksheet calculation test, not a new CSV-import path or a Windows/English-interface test. Read the dated test notes.

References

Microsoft documents XLOOKUP, including exact matching and supported editions. The duplicate and missing-label checks here are part of this original practice workflow.