Join a mapping with exact lookups
Learn the reasoning. Try the task. Compare your answer.
Prepared for DataCareerHub · Curriculum version 2026-10-14.2
By the end of this lesson
- Use a unique mapping key instead of repeated nested category rules.
- Handle unknown keys, duplicate keys and blank mapped values explicitly.
- Compare XLOOKUP with a compatible INDEX and MATCH approach.
Use English formula names and comma separators as shown, adapting to your Excel locale. The solution uses INDEX/MATCH; XLOOKUP is optional. Native PivotTable practice is performed in your own Excel edition.
Understand the method
A lookup enriches a record with a value from another table. It is not a replacement for a sum over many matching rows. In this case, the Mapping sheet classifies web as Digital and store as Physical store. The mapping has one row per channel. Its key must be unique and its classification must be populated. These are fictional teaching labels, not externally verified customer categories.
Trace the lookup
Prepared C6 holds an order's channel. Mapping A6:A7 holds the keys and B6:B7 their classifications. MATCH(C6,'Mapping'!$A$6:$A$7,0) finds the position of an exact key. The final zero selects exact matching. INDEX('Mapping'!$B$6:$B$7,position) returns the classification at that position. Both mapping ranges remain fixed while the current row's C6 changes as you fill down.
Check the mapping contract
A first-match lookup can hide duplicates. A matched blank value may be returned as zero in spreadsheet calculations, which can look like a legitimate classification. The solution uses a visible guard: require exactly one matching key and exactly one nonblank matching classification before doing the lookup.
=IF(AND(COUNTIFS('Mapping'!$A$6:$A$7,C6)=1,COUNTIFS('Mapping'!$A$6:$A$7,C6,'Mapping'!$B$6:$B$7,"<>")=1),INDEX('Mapping'!$B$6:$B$7,MATCH(C6,'Mapping'!$A$6:$A$7,0)),"Review mapping")Read it in stages: count the key; count nonblank classifications for that key; if both counts equal one, return the exact classification; otherwise show Review mapping. This message deliberately remains visible. It is not silently replaced by zero or an empty string. Do not use a mapping-warning label to filter away records without reviewing the issue.
XLOOKUP when your edition supports it
A basic exact lookup can also be written as =XLOOKUP(C6,'Mapping'!$A$6:$A$7,'Mapping'!$B$6:$B$7,"Review mapping",0). Its not-found argument handles an unknown key, but that alone does not reject duplicate keys or blank mapped values. Keep the same mapping checks around any production lookup. The downloadable solution uses INDEX/MATCH for compatibility. Microsoft documents that XLOOKUP is not available in Excel 2016 or 2019.
Do not confuse joins with aggregation
Looking up the value of an order by a nonunique customer or channel would return only one match. If the question asks total completed sales for a channel, use SUMIFS or a correctly configured PivotTable. If you join tables at incompatible grains, test the row count and totals before and after. A perfectly functioning lookup can still implement the wrong analytical idea.
Formula examples use English function names and commas. Some Excel installations use localized names or semicolon argument separators. Adapt the syntax to your locale without changing the logic, ranges or exact-match requirement. Do not convert all comma characters in source CSV text to semicolons simply to make a formula paste work.
Practical exercise
Complete Prepared G6:G17 with the guarded INDEX/MATCH formula. Verify the classifications for F001 and F003. In a disposable copy test three exceptions: change C6 to unknown; duplicate the web key by changing Mapping A7 to web; clear Mapping B6. Test one exception at a time, observe the result and restore the original inputs before continuing.
Deliverable: the filled classification column and a test log explaining how each mapping exception stays visible. If XLOOKUP is available, compare the basic expression with the guarded one and explain the additional checks.
Open the worked solution
F001 is web and maps to Digital. F003 is store and maps to Physical store. Six of the 12 reference orders are web and six are store, so the completed classification column contains six Digital and six Physical store labels. Classification applies to all orders, not only completed ones.
Unknown C6 returns Review mapping because the key count is zero. Changing A7 to web makes web nonunique, so the same warning appears rather than selecting one match. Clearing B6 leaves one key but no populated classification, again producing Review mapping. Restore C6 to web, A7 to store and B6 to Digital. The reference workbook should contain no unresolved mapping warning.
One more question.
Microsoft references: XLOOKUP availability and syntax, import locale settings, refreshing PivotTable data.
How these learning materials are prepared and maintained · Report a correction: support@datacareerhub.io
Read and practice without an account. Sign in with an existing account to save lesson completion.
Next lesson →Fictional training data. Portfolio practice is not a claim of client work or an accredited qualification. Explore practical lessons, worked examples and projects. Follow a published path, practise the method and build work you can explain.